Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
You may be able to use Max/MaxString on Call date/time field
Use below formula as calculated Dimension for Call Date/Time.
ggr(MaxString([Call date/time ]), mob)
Hope it helps.
Correct Formula
Aggr(MaxString([Call date/time ]), mob)
Thanks for your reply. Cant we do this at the script level, as I dont want to bring back data that isnt useful.
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;
Thanks Krishnapriya.