null value for "TO_VALUE" in word mapping file?

[Zachary Wright]

The transmart-data loader ETL rejects any rows in the word mapping file where the "TO_VALUE" is set to null. Anyone know if there's a way to convert values to null, e.g. is there a magic keyword to represent null? Thanks.

--- Zach

[Peter Rice]

Hi Zach,

Do you have an example input you can share?

regards,

Peter Rice
Axiomedix Inc.

> --
> For more ways to get in contact with the tranSMART community visit
> https://wiki.transmartfoundation.org/display/transmartwiki/Getting+Support
> ---
> You received this message because you are subscribed to the Google
> Groups "transmart-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to transmart-disc...@googlegroups.com
> <mailto:transmart-disc...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/transmart-discuss/1729d543-e95a-49ca-9e30-bd65b880d028%40googlegroups.com
> <https://groups.google.com/d/msgid/transmart-discuss/1729d543-e95a-49ca-9e30-bd65b880d028%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Zachary Wright]

Hello Peter. For example, we want to convert the values "NA" and "NP" to nulls, so the relevant lines in the word mapping file are:

FILENAME COL_NBR FROM_VALUE TO_VALUE

H3_visits_v1.txt 23 NA (empty)

H3_visits_v1.txt 23 NP (empty)

But the loader is throwing this error:

2020/02/28 10:41:21 - Missing map values.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : Row nr 1 causing abort : [H3_patients_v1.txt], [24], [NA], [null], [H3_patients_v1.txt]

2020/02/28 10:41:21 - Missing map values.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : Word Mapping file is missing a from-value or to-value

[Peter Rice]

Hi Zach,

null (lower case) works for me as the to-value

That was using Kettle 8.2 and the latest transmart 19 code so your
mileage may vary. It got through the Kettle 'IS NULL' test and skipped
loading. The cz_jobv_audit table shows those rows are missing from the
'Loaded records' count.

Let me know if it does the trick, otherwise I can dive in deeper.

regards,

Peter Rice
Axiomedix Inc.

On 28/02/2020 16:35, Zachary Wright wrote:
> Hello Peter. For example, we want to convert the values "NA" and "NP" to
> nulls, so the relevant lines in the word mapping file are:
>
> FILENAME COL_NBR FROM_VALUE TO_VALUE
> H3_visits_v1.txt 23NA (empty)

> H3_visits_v1.txt 23NP (empty)

>
> But the loader is throwing this error:
>
> 2020/02/28 10:41:21 - Missing map values.0 - ERROR (version 5.1.0.0,
> build 1 from 2014-06-19_19-02-57 by buildguy) : Row nr 1 causing abort :
> [H3_patients_v1.txt], [24], [NA], [null], [H3_patients_v1.txt]
> 2020/02/28 10:41:21 - Missing map values.0 - ERROR (version 5.1.0.0,
> build 1 from 2014-06-19_19-02-57 by buildguy) : Word Mapping file is
> missing a from-value or to-value
>
>

> --
> For more ways to get in contact with the tranSMART community visit
> https://wiki.transmartfoundation.org/display/transmartwiki/Getting+Support
> ---
> You received this message because you are subscribed to the Google
> Groups "transmart-discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to transmart-disc...@googlegroups.com
> <mailto:transmart-disc...@googlegroups.com>.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/transmart-discuss/94700e37-4ca0-4d5a-9bd4-b1f58da9ed53%40googlegroups.com
> <https://groups.google.com/d/msgid/transmart-discuss/94700e37-4ca0-4d5a-9bd4-b1f58da9ed53%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Zach Wright]

Thanks Peter! Replacing the empty strings with the word "null" did the trick. 

-- Zach