# New to QlikView

Discussion board where members can get started with QlikView.

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

Could someone help me build this logic?

Thanks

Labels (4)

• ### script editor

1 Solution

Accepted Solutions
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:
[
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:
Product,
Max(Date) as Date
Resident
TempData
Group By
Product
;
Left Join(Data)
Product,
Date,
Max(Price) as Price
Resident
TempData
Group By
Product, Date
;
drop Table TempData;

5 Replies
Partner

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

Simple Group By should work for you:

TempData:
[
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:
Product,
Max(Date) as MaxDate,
Max(Price) as MaxPrice
Resident
TempData
Group By
Product
;

drop Table TempData;

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

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

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

I've changed dates for Banana. Works ok.

TempData:
[
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:
Product,
Max(Date) as MaxDate,
Max(Price) as MaxPrice
Resident
TempData
Group By
Product
;
drop Table TempData;

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:
[
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:
Product,
Max(Date) as Date
Resident
TempData
Group By
Product
;
Left Join(Data)
Product,
Date,
Max(Price) as Price
Resident
TempData
Group By
Product, Date
;
drop Table TempData;

Partner

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

Thank you, Rob!

I missed that point.