Latest Record Flag Using ApplyMap

    Hi,

     

    I often use data which has multiple records and the client is only interested in the latest one for reporting purposes.

     

    For example each day they count widgets and the customer wants to see the latest number (as that’s the most up-to-date) per widget type. Not every type of widget is counted every day so you need someway of determining the record for each type:

     

    Original Data.png

     

    In the script I use a mapping table grouped by Widget, the expression field is a composite key of [Widget] & Max([Day]) and '1' as the value to map to, thus creating a list of the latest records:

    Mapping Table.png

     

    When reloading the original data I use the applymap function and the same composite key as the expression:

    ApplyMap('Latest_Count_MAP',Widget &'_'& Day,0) As _Flag_Latest_Count

     

     

    If the record is found the flag is mapped to 1, all other records are set to zero as they can't be found.

     

    Hey presto we have the latest record flagged !

     

    Output.png

     

    I hope this example helps people, you can find more examples on my blog www.qlikcentral.com.

     

    Take care

     

    Richard