topic Re: NullAsValue for null values coming from join of tables in App Development

NullAsValue for null values coming from join of tables

Anonymous — Fri, 18 May 2018 12:01:35 GMT

I have 2 tables with data that can be joined using ID column. The log table is missing some IDs which are present in the inc table. I'd like to have one single parameter that would allow me to select rows present in both tables.

My idea was to substitute nulls that appears when joining the data (in this case, ID = 3 does not appear in log) with something that can be selected. I tried to use NullAsValue, but it still appears as '-' and cannot be selected. Any ideas?

Qualify *;

Unqualify ID;

inc:

LOAD * INLINE [

ID, inc_p

1, a

2, b

3, b

4, c

];

Unqualify *;

Set NullValue = 'n/a';

NullAsValue uID;

NoConcatenate

temp:

LOAD * INLINE [

ID, log_p, uID

1, x, u1

1, y, u2

1, z, u3

2, y, u4

2, t, u5

4, x, u6

4, y, u7

4, z, u8

4, t, u9

];

Qualify *;

Unqualify ID;

NoConcatenate

log:

LOAD *,

If(Match(log_p, 'x', 'y') > 0, 'Y', 'N') AS log_p2

Resident temp;

Unqualify;

Drop Table temp;

Re: NullAsValue for null values coming from join of tables

bramkn — Fri, 18 May 2018 12:49:42 GMT

Use inner join or inner keep. to get only the values that are in both tables.

Re: NullAsValue for null values coming from join of tables

Anonymous — Fri, 18 May 2018 13:02:52 GMT

I want to keep the data but use the selection in some cases

Re: NullAsValue for null values coming from join of tables

bramkn — Fri, 18 May 2018 13:31:27 GMT

resident load and join on normal data

Re: NullAsValue for null values coming from join of tables

Anonymous — Fri, 18 May 2018 13:58:03 GMT

can you explain that a little bit more...?

Re: NullAsValue for null values coming from join of tables

isingh30 — Fri, 18 May 2018 14:04:17 GMT

Please share your data or sample application.

Thanks.

Re: NullAsValue for null values coming from join of tables

Anonymous — Fri, 18 May 2018 14:04:57 GMT

I attached the script with sample data inline

Re: NullAsValue for null values coming from join of tables

bramkn — Fri, 18 May 2018 14:10:28 GMT

create temp tables with just the ID's for example. and then add a field with for example "exists in both tables". inner join these two tables to get a table with ID's that exist in both tables. with this you can do what ever you want.

Re: NullAsValue for null values coming from join of tables

Anonymous — Fri, 18 May 2018 14:26:46 GMT

I've added to the script:

NoConcatenate

join:

Load ID, 'y' AS 'exist in both tables?' Resident inc;

Inner Join Load ID Resident log;

And looks like it's working! Thanks!

Anyway, I'm still curious why NullAsValue does not work... 🙂