topic Re: Max Record in QlikView

Max Record

paulwalker — Wed, 25 Nov 2020 16:16:04 GMT

HI All,

How to get max date record..

ID	Date	sales
100	1/1/2014	1000
100	1/15/2014	2500
100	2/2/2015	100
101	2/3/2015	890
101	5/13/2015	259
102	5/15/2015	500
102	5/16/2015	90

My output should be..

ID	Date	sales
100	2/2/2015	100
101	5/13/2015	259
102	5/16/2015	90

Thanks

Re: Max Record

Kushal_Chawda — Sun, 03 Apr 2016 16:25:18 GMT

Create the Straight Table

Dimension : ID

Expression :

Date : FirstSortedValue(Date, -Date)

Sales : FirstSortedValue(Sales, -Date)

Re: Max Record

swuehl — Sun, 03 Apr 2016 16:30:09 GMT

If you want a script solution:

LOAD ID,

LastValue(Date) as Date,

LastValue(Sales)

RESIDENT YourTable

GROUP BY ID

ORDER BY ID, Date asc;

DROP TABLE YourTable;

Re: Max Record

Chanty4u — Sun, 03 Apr 2016 16:31:59 GMT

plz chk

Re: Max Record

paulwalker — Sun, 03 Apr 2016 16:36:53 GMT

Thanks for Quick response....

Suppose some times i have data like this..

ID	Date	sales
100	1/1/2014	1000
100	2/2/2015	2500
100	2/2/2015	100
101	2/3/2015	890
101	5/13/2015	259
102	5/15/2015	500
102	5/16/2015	90

My output should be..

ID	Date	sales
100	2/2/2015	2600
101	5/13/2015	259
102	5/16/2015	90

Thanks...

Re: Max Record

swuehl — Sun, 03 Apr 2016 16:43:46 GMT

Maybe like

...

Left JOIN (YourTable)

LOAD ID,

Date(Max(Date)) as Date,

1 as Flag

RESIDENT YourTable

GROUP BY ID;

RESULT:

LOAD ID, Date, Sum(sales) as sales

RESIDENT YourTable

WHERE Flag =1

GROUP BY ID, Date;

DROP TABLE YourTable;

Re: Max Record

paulwalker — Sun, 03 Apr 2016 16:48:27 GMT

Thank You