In this post we’ll dive in the world of mapping loads. Mapping loads can be used for replacing values in your Qlik script. If you think, I’ve seen this before on DataOnThe.Rocks.. Yes, you’re right! Michael Steedle has written a post on the “Best match” in Qlik Script. Make sure to check that one also out.
Mapping loads & Loops, let them play together
The issue is that mapping loads and loops are not always best friends. They can mess each other up if you don’t learn them how to play together. To start, get the standard script from Qlik using CTRL + 00. This will auto generate a script for you that is useful with testing purposes.
Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha,
RecNo() as Num
autogenerate 26;
ASCII:
Load
if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
Chr(RecNo()) as AsciiAlpha,
RecNo() as AsciiNum
autogenerate 255
Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;
Transactions:
Load
TransLineID,
TransID,
mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round( 10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;
Comment Field Dim1 With "This is a field comment";
After that, remove two tables because we don’t need them in this exercise.
Drop tables Characters, ASCII;
Use loops for reducing the amount of code you need to write
Being a developer, you want to write as less code as possible to achieve your goal. Loops are great for reducing the amount of script needed to achieve this. In our example today we want to re-apply our expressions to the table. We’ll do this to keep the example simple and easy to understand.
The next step is to create a simple inline table where we are going to define the three rounds needed for our loop.
RoundsTable: Load * Inline [ Rounds 1 2 3 ];
So our loop will run for 3 rounds. Let’s start creating the loop. In the comments below can see what the script is doing.
// Create the loop and vRound will be getting the values from the field Rounds
For each vRound in FieldValueList('Rounds')
// A mapping is going to be created each time we loop through the file
mapKey2Dim:
Mapping Load
Num & '|' & TransID & '|' & TransLineID as Key,
// Because we want to pick up a new field each round, we put the variable in the name. First it will get Expression1, then Expression2 etc.
Expression$(vRound) as Result
Resident Transactions
;
// Finally we are going to apply the mapping. Outcome will create 3 fields, first Outcome1, then Outcome2 etc.
Final:
Load
*,
ApplyMap('mapKey2Dim', Num & '|' & TransID & '|' & TransLineID, 'Nothing found' ) as Outcome$(vRound)
Resident Transactions
;
// Drop the original table because we don't need it anymore. We want the new 'Final' table, because this contains our new created fields
Drop table Transactions;
// We have to rename the table because otherwise on the 2nd run, the table 'Transactions' will not be found
Rename table Final to Transactions;
// Go to the next value in Rounds
Next vRound
Let see what the outcome is of this execution:
The outcome of the ‘Outcome’ fields are not correct. We can see that the first field is applied 3 times and we are not seeing Expression2 in Outcome2 and Expression3 in Outcome3. How can this be the case? And how can we solve this?
First answering the first question, the mapping tables are auto concatenated. Meaning, when a new mapping table is created it’s added underneath the earlier mapping table. When being applied, it will always get the values from the first mapping and therefore you’ll never get the outcomes from Expression2 and Expression3.
Answering the second question, you maybe think because you see the word ‘auto concatenate’, we can use the statement NoConcatenate. This works fine for tables, but tables are NOT the same as mapping tables. Tables have the feature that they are concatenated when all field names are equal. If you don’t want this to happen, you just simple add NoConcatenate before the Load statement.
This is not the case for mapping tables, because field names don’t matter with mapping tables. That is why you often see ‘Key and Result’ as the field names for mapping tables. Mapping tables are differentiated based on their table name. This is also hints to our solution!
Solving our issue can be done by adding the variable vRounds to the table name. Creating an unique table name. Don’t also forget to add it in the ApplyMap! Let see when we apply this:
// Create the loop and vRound will be getting the values from the field Rounds
For each vRound in FieldValueList('Rounds')
// A mapping is going to be created each time we loop through the file
mapKey2Dim_$(vRound):
Mapping Load
Num & '|' & TransID & '|' & TransLineID as Key,
// Because we want to pick up a new field each round, we put the variable in the name. First it will get Expression1, then Expression2 etc.
Expression$(vRound) as Result
Resident Transactions
;
// Finally we are going to apply the mapping. Outcome will create 3 fields, first Outcome1, then Outcome2 etc.
Final:
Load
*,
ApplyMap('mapKey2Dim_$(vRound)', Num & '|' & TransID & '|' & TransLineID, 'Nothing found' ) as Outcome$(vRound)
Resident Transactions
;
// Drop the original table because we don't need it anymore. We want the new 'Final' table, because this contains our new created fields
Drop table Transactions;
// We have to rename the table because otherwise on the 2nd run, the table 'Transactions' will not be found
Rename table Final to Transactions;
// Go to the next value in Rounds
Next vRound
Now we can see that the Outcome2 and Outcome3 are applied correctly.
Take away
Remember that mapping tables are auto concatenated based on their name and in combination with loops, you need to add the loop variable in the table name.
