Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
katarzyna_wojci
Contributor II
Contributor II

NullAsValue for null values coming from join of tables

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;

1 Solution

Accepted Solutions
bramkn
Partner - Specialist
Partner - Specialist

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.

View solution in original post

8 Replies
bramkn
Partner - Specialist
Partner - Specialist

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

katarzyna_wojci
Contributor II
Contributor II
Author

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

bramkn
Partner - Specialist
Partner - Specialist

resident load and join on normal data

katarzyna_wojci
Contributor II
Contributor II
Author

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

isingh30
Specialist
Specialist

Please share your data or sample application.

Thanks.

katarzyna_wojci
Contributor II
Contributor II
Author

I attached the script with sample data inline

bramkn
Partner - Specialist
Partner - Specialist

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.

katarzyna_wojci
Contributor II
Contributor II
Author

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