Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Item | key |
A | 1 |
B | 2 |
C | 3 |
Table 2, load using Left Keep ([Table 1])
Dim | key |
X1 | 1 |
X3 | 3 |
X4 | 4 |
Then in my dashboard, I have a table like this:
Item | Dim |
A | X1 |
B | - |
C | X3 |
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.
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
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
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 🙂
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