Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

qliktech_uk
New Contributor III

Retrieving the latest record for dimension values.

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.

Tags (1)
1 Solution

Accepted Solutions
krishnacbe
Valued Contributor III

Re: Retrieving the latest record for dimension values.

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;

6 Replies
vvira1316
Valued Contributor II

Re: Retrieving the latest record for dimension values.

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

krishnacbe
Valued Contributor III

Re: Retrieving the latest record for dimension values.

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

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


Hope it helps.

krishnacbe
Valued Contributor III

Re: Retrieving the latest record for dimension values.

Correct Formula

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

qliktech_uk
New Contributor III

Re: Retrieving the latest record for dimension values.

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

krishnacbe
Valued Contributor III

Re: Retrieving the latest record for dimension values.

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
New Contributor III

Re: Retrieving the latest record for dimension values.

Thanks Krishnapriya.