Flag Latest Record using QlikView Script

· by · in Create, Scripting. ·

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.

QlikView Code

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

Mapping 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

QlikView Latest Flag Output

QlikView Latest Flag Output

Note: I’ve recorded the videos in highest resolution possible. So please view them in 1080 or 720P (HD) for best quality. Cheers!

https://www.youtube.com/watch?v=IZhdtQdsGg4

 

Happy Qliking
Richard

6 responses to “Flag Latest Record using QlikView Script”

  2. Pingback: Joins, Concatenation and the Applymap function | qlikcentral·

  4. 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.

    Like

    • Hi Mike.

      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.

      Cheers. Rich

      Like

Leave a comment