Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
btrinh
Contributor II
Contributor II

Assign values for unmatched records in LEFT KEEP

Hello,

I have run into a problem while using Left Keep, and I hope someone here could help me with it 🙂 

I have two tables like this: 

Table 1

Itemkey
A1
B2
C3

 

Table 2, load using Left Keep ([Table 1])

Dimkey
X11
X33
X44

 

Then in my dashboard, I have a table like this:

ItemDim
AX1
B-
CX3

 

I want to assign a default Dim value ("missing", for example) for records like B. Is there anyway to do this?

I know using Join will solve this problem, but I'd really prefer to use Keep to help with the performance, since my data is quite big. The real Table 1 has some 50 fields with ~ 1M records (and still growing), and I have a couple more tables like that in my app. 

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

It's not possible in this way respectively within this step - a join/keep will match or not but each further computation needs another load-statement. Beside this I doubt that the keep approach will really help you in regard of performance. Personally I would probably use a mapping, for example:

m: mapping load key, Dim from table2;

t: load *, applymap('m', key, 'missing') as Dim from table1;

- Marcus

View solution in original post

4 Replies
marcus_sommer

It's not possible in this way respectively within this step - a join/keep will match or not but each further computation needs another load-statement. Beside this I doubt that the keep approach will really help you in regard of performance. Personally I would probably use a mapping, for example:

m: mapping load key, Dim from table2;

t: load *, applymap('m', key, 'missing') as Dim from table1;

- Marcus

btrinh
Contributor II
Contributor II
Author

thank you for your advice! I have to use multiple mapping tables since my Table 2 actually has many fields, but they all work perfectly 🙂 

marcus_sommer

You could reduce the number of needed mapping-tables by merging several fields into a single one and picking then the appropriate value from there. It's a logic which I use quite often and by rather many fields is a supporting variable as a customized function very helpful, too - for example with something like:

set eMap = "subfield(applymap($1, $2, repeat('|', $4 - 1) & '$3|'), '|', $4)";

m: mapping load key, Dim1 & '|' & Dim2 from table2;

t: load *, $(eMap('m', key, missing, 1) as Dim1,  $(eMap('m', key, #NV, 2) as Dim2 from table1;

- Marcus

btrinh
Contributor II
Contributor II
Author

I'll try it out! thanks a lot!