topic Re: Aggregate in Script in QlikView

Aggregate in Script

Tue, 16 Dec 2014 14:18:27 GMT

Hi,

I have a scenario with data like below;

CounterID

Date

04/10/2014 22:38:26

04/10/2014 22:39:26

04/10/2014 22:51:09

06/10/2014 13:56:51

04/10/2014 22:38:26

04/10/2014 22:39:26

04/10/2014 22:45:00

And I need to fetch the Latest Date for each CounterID in the SCRIPT and not in the expression.

so that I get a field with Latest Dates for each ID;

CounterID	Date
1	04/10/2014 22:51:09
2	06/10/2014 13:56:51
3	04/10/2014 22:45:00

I tried using Peek grouping by CounterID, but not getting the desired Result.

Appreciate any

Help on the same !

Re: Aggregate in Script

saumyashah90 — Tue, 16 Dec 2014 14:23:31 GMT

File1:

Load

CounterID,

Date(Subfield(Date,' ',1),'DD/MM/YYYY') as Dt

from xyz .

File2:

Load

CounterID,

Max(Dt)

resident File1 group by CounterID;

//If not needed

Drop table File1;

Re: Aggregate in Script

Tue, 16 Dec 2014 14:24:46 GMT

firstsortedvalue(counterid,-date) - u ll get max values

Re: Aggregate in Script

MK_QSL — Tue, 16 Dec 2014 14:42:58 GMT

T1:

Load

CounterID,

Timestamp(Date) as Date

Inline

[

CounterID, Date

1, 04/10/2014 22:38:26

1, 04/10/2014 22:39:26

1, 04/10/2014 22:51:09

2, 06/10/2014 13:56:51

3, 04/10/2014 22:38:26

3, 04/10/2014 22:39:26

3, 04/10/2014 22:45:00

];

Left Join

Load

CounterID,

FirstSortedValue(Date,-Date) as MaxDate

Resident T1

Group By CounterID;

Re: Aggregate in Script

MarcoWedel — Tue, 16 Dec 2014 16:44:43 GMT

table2:
LOAD
  CounterID,
  Timestamp(Max(Date)) as MaxDate
Resident table1
Group By CounterID;

Re: Aggregate in Script

its_anandrjs — Tue, 16 Dec 2014 18:18:28 GMT

Hi,

There is another way of doing the same without change the timestamp format

Tab1:
Load
CounterID,
Timestamp#(Date) as Date
Inline
[ CounterID, Date
1, 04/10/2014 22:38:26
1, 04/10/2014 22:39:26
1, 04/10/2014 22:51:09
2, 06/10/2014 13:56:51
3, 04/10/2014 22:38:26
3, 04/10/2014 22:39:26
3, 04/10/2014 22:45:00
];

Tab2:
Load
CounterID,
Date#(MaxString(Date),'DD/MM/YYYY hh:mm:ss') as MaxDateField
Resident Tab1
Group By CounterID;

Regards

Anand

Re: Aggregate in Script

MarcoWedel — Tue, 16 Dec 2014 21:19:13 GMT

Hi,

one solution might be:

tabDates:

LOAD * INLINE [

CounterID, Date

1, 04/10/2014 22:38:26

1, 04/10/2014 22:39:26

1, 04/10/2014 22:51:09

2, 06/10/2014 13:56:51

3, 04/10/2014 22:38:26

3, 04/10/2014 22:39:26

3, 04/10/2014 22:45:00

];

tabLatestDates:

LOAD CounterID,

Timestamp(Max(Date)) as MaxDate

Resident tabDates

Group By CounterID;

Re: Aggregate in Script

rwunderlich — Tue, 16 Dec 2014 21:51:57 GMT

I would guess that using MaxString() would be significantly slower than the max() or FirstSortedValue() suggestions given earlier.

-Rob