group by using max(Date) and additional condition

[alessandrotk]

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

[bgerchikov]

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;

[alessandrotk]

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!

[bgerchikov]

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;

[rwunderlich · Accepted solution]

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

[bgerchikov]

Thank you, Rob!

I missed that point.