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;
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;
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:
