Retrieving the latest record for dimension values.

[qliktech_uk]

Hi,

I have data for mobile numbers along with the latest call date/time. I want to capture the mobile number with the latest time.

Eg:

Mob   Call date/time 

ccc     25/09/2017 10:00:00

ccc     24/09/2017 10:00:00

xxx     25/09/2017 10:00:00

bbbb   25/09/2017 10:00:00

My report should be

Mob   Call date/time 

ccc     25/09/2017 10:00:00

xxx     25/09/2017 10:00:00

bbbb   25/09/2017 10:00:00

I need to exclude ccc and pick up only the laatest record for ccc.

[vvira1316]

You may be able to use Max/MaxString on Call date/time field

[krishnacbe]

Use below formula as calculated Dimension for Call Date/Time.

ggr(MaxString([Call date/time ]), mob)

Hope it helps.

[krishnacbe]

Correct Formula

Aggr(MaxString([Call date/time ]), mob)

[qliktech_uk]

Thanks for your reply. Cant we do this at the script level, as I dont want to bring back data that isnt useful.

[krishnacbe · Accepted solution]

Try below script.

Temp_Data:
LOAD Mob,
[Call date/time ]
FROM TestData.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Data:
Load Mob,
MaxString([Call date/time ]) as [Call date/time]
Resident Temp_Data
Group by Mob;

drop Table Temp_Data;

[qliktech_uk]

Thanks Krishnapriya.