NullAsValue for null values coming from join of tables

[Anonymous]

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;

[bramkn]

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

[Anonymous]

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

[bramkn]

resident load and join on normal data

[Anonymous]

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

[isingh30]

Please share your data or sample application.

Thanks.

[Anonymous]

I attached the script with sample data inline

[bramkn · Accepted solution]

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.

[Anonymous]

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... 🙂