topic Re: Assign values for unmatched records in LEFT KEEP in App Development

Assign values for unmatched records in LEFT KEEP

btrinh — Sat, 16 Nov 2024 20:00:30 GMT

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.

Re: Assign values for unmatched records in LEFT KEEP

marcus_sommer — Thu, 26 Sep 2019 15:00:23 GMT

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

Re: Assign values for unmatched records in LEFT KEEP

btrinh — Fri, 27 Sep 2019 08:36:51 GMT

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 🙂

Re: Assign values for unmatched records in LEFT KEEP

marcus_sommer — Fri, 27 Sep 2019 09:20:00 GMT

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

Re: Assign values for unmatched records in LEFT KEEP

btrinh — Fri, 27 Sep 2019 10:41:03 GMT

I'll try it out! thanks a lot!