Summary: Can you concatenate rows to a Mapping Table? The answer is “Yes” and I show you how.
Recently my Masters Summit colleague Oleg Troyanksy asked me “What are the limits on updating an existing Mapping Table in Qlik script?”. My immediate answer was “You can’t update a Mapping Table after it’s been created”. Then Oleg showed me a script that surprised me — adding to a mapping table in a loop, implicit concatenation. The loop works, but what if we need to load from multiple sources?
When the contents of a mapping table come from multiple Load statements, I have always advised doing standard loads to a temp table and then a final Mapping Load from the temp table.
Turns out you can concatenate to a mapping table. Maybe that temp table is unnecessary. I don’t find any doc on the topic. Here’s what I’ve found from experimentation.
We may wish to do something like this:
MapX: Mapping Load * Inline [ from, to 1, A 2, B ]; Concatenate (MapX) Mapping Load * Inline [ from, to 3, C ];
That script will fail with “Table ‘Mapx’ not found” error. You cannot directly reference a mapping table this way.
Interestingly, if we leave off the “Concatenate (MapX)”, it will concatenate and result in the desired mapping. Implicit concatenation will kick-in and the second load will add rows to the mapping table. I’ve included some ApplyMap() code in this example so you can copy/paste and test for yourself.
MapX: Mapping Load * Inline [ from, to 1, A 2, B ]; Mapping Load * Inline [ from, to 3, C ]; Data: Load RecNo() as Recid, ApplyMap('MapX', RecNo()) as Mapped AutoGenerate 5;
The resulting output looks like this, proving that 3/C has been added to the map.
Unlike implicit concatenation for standard tables, the fieldnames need not be the same. This script will concatenate. Note the different fieldnames in the second load.
MapX: Mapping Load * Inline [ from, to 1, A 2, B ]; Mapping Load * Inline [ X, Y 3, C ];
What if there is an intervening standard load? Will concatenation occur?
MapX: Mapping Load * Inline [ from, to 1, A 2, B ]; Fact: LOAD 1 as X AutoGenerate 1; Mapping Load * Inline [ X, Y 3, C ];
The answer is no, concatenation will not happen. The second Mapping Load will create a new invisible mapping table.
So if I can’t name the mapping table in a Concatenate prefix, is there some other way to explicitly concatenate? Turns out there is. This will work.
MapX: Mapping Load * Inline [ from, to 1, A 2, B ]; Fact: LOAD 1 as X AutoGenerate 1; MapX: Mapping Load * Inline [ X, Y 3, C ];
When naming the mapping table again with the same label. explicit concatenation will occur! This is unlike a standard load where repeating a label results in a new table with “-1” suffix (when fieldnames don’t match).
In summary, you can add rows to a mapping table. Repeating the table label again will ensure you are adding, whether there is an intervening standard load or not.
Now you may be wondering …can I do an implied Join? I think not.
On Jan 20, 2021 I’ll be sharing more scripting tips and techniques in my “Advanced Scripting” on-line course as part of the “Masters Summit at Home” series. Join me there for more on topics like creating reusable script and maintaining optimized loads.
Very Good
It’s also interesting what will happen if we add a new value for the existing key:
MapX:
Mapping Load * Inline [
from, to
1, A
2, B
];
Mapping Load * Inline [
from, to
2, D
3, C
];
Data:
Load RecNo() as Recid,
ApplyMap(‘MapX’, RecNo()) as Mapped
AutoGenerate 5;
Mapped Recid
A 1
B 2
C 3
4 4
5 5
As you can see key 2 is still B.
Therefore, we need to be careful when loading a mapping table inside a loop. I recently investigated a similar incident, I assumed that each mapping load erases the old table and creates a new one, but no. If you have the same keys, you will get the values from the initial load.
To avoid this problem, just add the iteration number to the mapping table name.
I’ve been doing this for some time. It’s really helpful. I’ve also satisfied myself that if you define the same lookup multiple times then it’s always the first entry in the mapping table that gets returned.
This is handy for requirements along the lines of “If it’s in this list then use value from table A, otherwise use value from table B, then as a last resort try table C”. Much neater than nested ApplyMaps, and you don’t need to worry about making a temporary table first with a “where not exists()” clause.
I think the mapping table has both values but ApplyMap uses first entry as documented.
This is a standard behaviour of an ApplyMap. It uses the first value found. Isn’t it?
Hi Rob,
Thanks for sharing.
Sergy has already kinda beaten me to the point that I was going to make about duplicates, but I was going to come at it from another angle.
As ApplyMap tables always return the first sorted result from a mapping table you can concatenate from different lists in an order of precedence – so you can say try looking up in this table, but if that doesn’t work out use the value from this one, then that one. This is done simply by concatenating in the right order.
Steve
Also, if you give a name to the second Mapping Load that comes after the first one (and that implicitly concatenates – with the same field names or not), it will NOT concatenate, if the name is different from MapX.
This also differs from normal tables (in case of loading fields with the same name and count), where the second will concatenate to the first, even if you name them differently.