Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
alessandrotk
New Contributor

group by using max(Date) and additional condition

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).

max_group.jpg

Could someone help me build this logic?

Thanks

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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

5 Replies
Partner
Partner

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

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
New Contributor

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

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).

max_group.jpg

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!

Partner
Partner

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

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;

 

 

MVP & Luminary
MVP & Luminary

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

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

Partner
Partner

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

Thank you, Rob!

I missed that point.