Often you’ll have a number of records for effectively the same thing and you’re only interested in the latest one.
For example each day you count widgets and the customer wants to see the latest number (as that’s the most up-to-date).
Applying a flag to the data makes this calculation very simple. It has a second advantage if you’re requirement is to only load the latest information into the Dashboard.
In this code we create a mapping table with the Max(Day) grouped by Widget. We’re left with two records which correspond to the latest record for each widget in the original table
Because we create a composite key of the two fields we’re able to link this back to the original data using ApplyMap and setting the flag to 1. Any records that can’t be found in the mapping table (all those which aren’t the lastest records) are given a zero value for the Flag
Note: I’ve recorded the videos in highest resolution possible. So please view them in 1080 or 720P (HD) for best quality. Cheers!
this example could be helpful (in spanish):
Thanks Enmanuel. Thanks for the link also. Richard
Pingback: Joins, Concatenation and the Applymap function | qlikcentral·
Good stuff, worked exactly as expected for me. Thank you!
I would consider left joining your current mapping load directly back to the original table. This would allow you to skip the last step (less code) and would also execute faster.
Yes taking one load step out makes sense. I did it this way mainly for the example.
Interesting though I test left join against mapping on a large dataset once and mapping is quicker! So if I only have one field I tend to use an applymap rather than a left join although yes taking out the extra step would make it faster.