Qlik Community

QlikView Documents

Documents for QlikView related information.

Latest Record Flag Using ApplyMap

richard_pearce6
Valued Contributor

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

Attachments
Comments
mazacini
Contributor III

Hi Richard

Thank you for sharing this - it looks like it could solve an issue I am facing.

Could you possibly publish the script from your qvw file?

I only have PE version.

Many thanks

Joe

richard_pearce6
Valued Contributor

Hi Joe,

Its looks something like this:

FirstRecord:
LOAD 
     FACT_ID & min(TimeStamp) as Key

FROM
qvdfile.qvd (qvd)
group by Date, Product, Etc;

Data:
LOAD
*
    
FROM qvdfile.qvd (qvd)
where Exists(Key,FACT_ID  & num(TimeStamp));

richard_pearce6
Valued Contributor

Sorry my last email just brings in those records, here is the code to flag it:

FirstRecord_MAP:
Mapping
LOAD 
     FACT_ID & min(TimeStamp) as Key
     1 As Flag

FROM
qvdfile.qvd (qvd)
group by Date, Product, Etc;

Data:
LOAD
*
,ApplyMap('FirstRecord_MAP',FACT_ID & num(TimeStamp),0) as _Flag_FirstReord
    
FROM qvdfile.qvd (qvd)
;

mazacini
Contributor III

Many thanks Richard.

It works perfectly.

I had major problems with the date/time aspect of my model. But that I assume is down to my lack of expertise.

Eventually, I created an "instance" for each FACT_ID, and built the apply map around the Max Instance number.

Thanks Again

Joe

richard_pearce6
Valued Contributor

Good to hear you have it working Joe, perhaps the issue you were facing was because your time feild needed to be converted to the correct format ie Date#() ...

Richard

Version history
Revision #:
1 of 1
Last update:
‎08-21-2014 05:06 AM
Updated by: