topic Re: Max Data with Time in QlikView

Max Data with Time

sumeet-vaidya — Thu, 15 Mar 2018 09:22:33 GMT

Dear All,

Please find below data. I need to Pick the max date and associated time for the given ID in script.

ID	Date	Time	Name
101	01-02-2017	11:04:25	ABC
101	04-05-2017	18:12:14	PQR
101	07-08-2017	09:45:45	XYZ
101	07-08-2017	09:45:50	LMN

The result should be :

ID	Date	Time	Name
101	07-08-2017	09:45:50	LMN

Regards

Sumeet

stalwar1‌

Re: Max Data with Time

MK_QSL — Thu, 15 Mar 2018 09:48:27 GMT

Data:
Load
 ID,
 Date(Date#(Date,'DD-MM-YYYY')) as Date,
 Time,
 Name
Inline
[
 ID, Date, Time, Name
 101, 01-02-2017, 11:04:25, ABC
 101, 04-05-2017, 18:12:14, PQR
 101, 07-08-2017, 09:45:45, XYZ
 101, 07-08-2017, 09:45:50, LMN
];


Left Join (Data)
Load ID, Date(Max(Date)) as MaxDate Resident Data Group By ID;


Left Join (Data)
Load ID, Time(Max(Time)) as MaxTime Resident Data Where Date = MaxDate Group By ID;


NoConcatenate
Final:
Load ID, Date, Time, Name Resident Data
Where Date = MaxDate and Time = MaxTime;


Drop Table Data;

Re: Max Data with Time

tresB — Thu, 15 Mar 2018 09:52:20 GMT

Try using firstsortedvalue(), like:

Re: Max Data with Time

MK_QSL — Thu, 15 Mar 2018 09:54:57 GMT

Yeah that's the easiest way.. But Sumeet wants to achieve at script level.. Otherwise this is easy to do at front end.

Re: Max Data with Time

tresB — Thu, 15 Mar 2018 09:58:51 GMT

For script try like:

t1:

Load * Inline [

ID, Date, Time, Name

101, 01-02-2017, 11:04:25, ABC

101, 04-05-2017, 18:12:14, PQR

101, 07-08-2017, 09:45:45, XYZ

101, 07-08-2017, 09:45:50, LMN];

NoConcatenate

t2:

load

ID,

FirstSortedValue(Date, -(Date+Time)) as Date,

FirstSortedValue(Time, -(Date+Time)) as Time,

FirstSortedValue(Name, -(Date+Time)) as Name

Resident t1 Group by ID;

Drop Table t1;

Re: Max Data with Time

MK_QSL — Thu, 15 Mar 2018 10:07:07 GMT

Excellent.. This is better solution. But issue in using FirstSortedValue is ...

say we have below one more line.

101, 07-08-2017, 09:45:50, XXX

In this case, we will not get result.

If we use Distinct then will get one line one...

But we need here two lines...

So I avoided FirstSortedValue..

Re: Max Data with Time

tresB — Thu, 15 Mar 2018 10:12:21 GMT

......

But we need here two lines...

Lets wait to know OP's expectation.

Re: Max Data with Time

sumeet-vaidya — Thu, 15 Mar 2018 10:39:03 GMT

Thanks Manish, That works perfectly for me.

Regards

Sumeet

Re: Max Data with Time

sumeet-vaidya — Thu, 15 Mar 2018 10:40:50 GMT

Hi Tresco,

Thanks for your reply but i wanted to achieve it at the script level.

Regards

Sumeet

Re: Max Data with Time

MK_QSL — Thu, 15 Mar 2018 10:41:32 GMT

If you got your answer, then kindly close the thread by selecting correct/helpful answer.