topic Re: group by using max(Date) and additional condition in QlikView

group by using max(Date) and additional condition

alessandrotk — Sat, 16 Nov 2024 04:51:13 GMT

Hi.

I need to create a table (inside script editor) based on max(Date) available for each product and considering max(Price) value in case I have multiple prices for the same max(Date).

Could someone help me build this logic?

Thanks

Re: group by using max(Date) and additional condition

bgerchikov — Thu, 03 Jan 2019 17:46:23 GMT

Simple Group By should work for you:

TempData:
Load * Inline
[
Product, Date, Price
Apple,43404,15
Apple,43403,15
Apple,43402,20
Apple,43401,14
Banana,43404,100
Banana,43404,150
Banana,43402,70
Banana,43401,70
Banana,43400,70
Banana,43339,50
]
;
Data:
NoConcatenate Load
Product,
Max(Date) as MaxDate,
Max(Price) as MaxPrice
Resident
TempData
Group By
Product
;

drop Table TempData;

Re: group by using max(Date) and additional condition

alessandrotk — Thu, 03 Jan 2019 17:57:39 GMT

Hi,

Thanks for your help, but I need the max(Date) record and, in the same condition, I need the max(Price) within max(Date).

Just like the last table on the right side of the picture (expected output).

In your example it's getting the max(Date) and max(Price) separated for each product.

Thanks!

Re: group by using max(Date) and additional condition

bgerchikov — Thu, 03 Jan 2019 18:09:54 GMT

I've changed dates for Banana. Works ok.

TempData:
Load * Inline
[
Product, Date, Price
Apple,43404,15
Apple,43403,15
Apple,43402,20
Apple,43401,14
Banana,42404,100
Banana,42404,150
Banana,42402,70
Banana,42401,70
Banana,42400,70
Banana,42339,50
]
;
Data:
NoConcatenate Load
   Product,
   Max(Date) as MaxDate,
   Max(Price) as MaxPrice
Resident
   TempData
Group By
   Product
;
drop Table TempData;

Re: group by using max(Date) and additional condition

rwunderlich — Thu, 03 Jan 2019 20:27:00 GMT

I think you have to do the max() in two passes to cover the case where max Price may not be on max Date. For example in the case below max price for Banana (180) occurs on an earlier day, but the OP asked for the max price on the max date.

TempData:
Load * Inline
[
Product, Date, Price
Apple,43404,15
Apple,43403,15
Apple,43402,20
Apple,43401,14
Banana,42404,100
Banana,42404,150
Banana,42402,70
Banana,42401,70
Banana,42400,70
Banana,42339,180
]
;
Data:
NoConcatenate Load
Product,
Max(Date) as Date
Resident
TempData
Group By
Product
;
Left Join(Data)
LOAD
Product,
Date,
Max(Price) as Price
Resident
TempData
Group By
Product, Date
;
drop Table TempData;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: group by using max(Date) and additional condition

bgerchikov — Thu, 03 Jan 2019 20:37:19 GMT

Thank you, Rob!

I missed that point.