Latest Record Flag Using ApplyMap



    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 !




    I hope this example helps people, you can find more examples on my blog


    Take care