topic Max of two date fields in QlikView

Max of two date fields

andrew001 — Sun, 21 Dec 2014 19:32:16 GMT

Hi,

I have this table in input (tbl_INPUT), and I need to find, for each ID, the max DATE and, within the same date, the max DATE_INSERT (system timestamp).

thanks

Andrew

tbl_INPUT:

LOAD *
inline
[
ID, DATE, DATE_INSERT
1230,31-dic-09,14/08/2008 13:19
1230,31-dic-09,01/08/2010 16:13
1235,14-ago-08,10/08/2008 17:19
1235,14-ago-08,10/08/2008 16:11
1236,01-gen-09,02/02/2009 12:18
];

the desired output:

1230,31-dic-09,14/08/2008 13:19
1235,14-ago-08,10/08/2008 17:19
1236,01-gen-09,02/02/2009 12:18

Re: Max of two date fields

maxgro — Sun, 21 Dec 2014 19:41:02 GMT

this is to get one row for every ID, the first order by DATE desc and DATE_INSERT desc

tbl_INPUT:

LOAD *

inline

[

ID, DATE, DATE_INSERT

1230,31-dic-09,14/08/2008 13:19

1230,31-dic-09,01/08/2010 16:13

1235,14-ago-08,10/08/2008 17:19

1235,14-ago-08,10/08/2008 16:11

1236,01-gen-09,02/02/2009 12:18

];

tbl_OUTPUT:

NoConcatenate

load

ID, DATE, DATE_INSERT

Resident tbl_INPUT

where ID <> peek(ID)

order by ID, DATE desc, DATE_INSERT desc;

DROP Table tbl_INPUT;

Re: Max of two date fields

MK_QSL — Sun, 21 Dec 2014 20:35:01 GMT

Use below script

tbl_INPUT:

LOAD

ID,

Date(Date#(DATE, 'DD-MMM-YY')) as DATE,

Timestamp#(DATE_INSERT,'DD/MM/YYYY hh:mm') as DATE_INSERT

inline

[

ID, DATE, DATE_INSERT

1230,31-dec-09,14/08/2008 13:19

1230,31-dec-09,01/08/2010 16:13

1235,14-aug-08,10/08/2008 17:19

1235,14-aug-08,10/08/2008 16:11

1236,01-jan-09,02/02/2009 12:18

];

Now create a Straight Table

Dimension

Expression

=FirstSortedValue(DATE_INSERT, -Aggr(Date(FirstSortedValue(DATE, -Aggr(Max(DATE),ID))),DATE))

Re: Max of two date fields

MK_QSL — Sun, 21 Dec 2014 20:54:17 GMT

or use below

Dimension

Expression

FirstSortedValue(DISTINCT Aggr(Max(DATE_INSERT),ID,DATE),-DATE)