topic Re: Retrieving the latest record for dimension values. in QlikView

Retrieving the latest record for dimension values.

qliktech_uk — Mon, 25 Sep 2017 17:18:43 GMT

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.

Re: Retrieving the latest record for dimension values.

vvira1316 — Mon, 25 Sep 2017 17:27:48 GMT

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

Re: Retrieving the latest record for dimension values.

krishnacbe — Mon, 25 Sep 2017 18:24:08 GMT

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

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

Hope it helps.

Re: Retrieving the latest record for dimension values.

krishnacbe — Mon, 25 Sep 2017 18:24:25 GMT

Correct Formula

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

Re: Retrieving the latest record for dimension values.

qliktech_uk — Mon, 25 Sep 2017 18:26:35 GMT

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

Re: Retrieving the latest record for dimension values.

krishnacbe — Mon, 25 Sep 2017 18:35:34 GMT

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;

Re: Retrieving the latest record for dimension values.

qliktech_uk — Tue, 26 Sep 2017 17:51:49 GMT

Thanks Krishnapriya.