Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Experts,
I have the following dummy data:
**EDITED DUMMY DATA FOR INCLUDING ANOTHER SCENARIO*
ItemID | FromDate | ToDate | MapPrice | PriceList | SalesPrice |
---|---|---|---|---|---|
00572-393 | 0 | 0 | 169.99 | 99999 | 475 |
00572-393 | 20170724 | 0 | 285 | 99999 | 268 |
00572-514 | 0 | 0 | 199.99 | 99999 | 475 |
00572-515 | 0 | 0 | 291 | 99999 | 485 |
00572-516 | 0 | 0 | 306 | 99999 | 510 |
00572-517 | 0 | 0 | 312 | 99999 | 520 |
00573-009 | 0 | 0 | 174.99 | 99999 | 250 |
00573-010 | 0 | 0 | 184.99 | 99999 | 264 |
00573-011 | 0 | 0 | 184.99 | 99999 | 264 |
00573-013 | 0 | 0 | 184.99 | 99999 | 264 |
00573-014 | 0 | 174.99 | 99999 | 250 | |
00573-016 | 0 | 0 | 174.99 | 99999 | 250 |
00573-017 | 0 | 0 | 174.99 | 99999 | 250 |
00573-022 | 0 | 0 | 184.99 | 99999 | 264 |
00573-022 | 20170725 | 0 | 279.99 | 99999 | 400 |
I have a straight table(with a lot of expressions) where I want to add Map Price and Sales Price as dimensions. My data brings in multiple records for certain Items.
If there are multiple records, the user should see only the max Map Price, and the corresponding Sales Price. So the expected output will be like this:
ItemID | MapPrice | SalesPrice |
---|---|---|
00572-393 | 285 | 268 |
00572-514 | 199.99 | 475 |
00572-515 | 291 | 485 |
00572-516 | 306 | 510 |
00572-517 | 312 | 520 |
00573-009 | 174.99 | 250 |
00573-010 | 184.99 | 264 |
00573-011 | 184.99 | 264 |
00573-013 | 184.99 | 264 |
00573-014 | 174.99 | 250 |
00573-016 | 174.99 | 250 |
00573-017 | 174.99 | 250 |
00573-022 | 279.99 | 400 |
I'm having a hard time achieving this - any help appreciated
Regards,
Benazir
(PFA the dummy QVW)
My bad, I mis-read your requirement... try this
=Aggr(Max(MapPrice), ItemID)
=Aggr(FirstSortedValue(SalesPrice, -MapPrice), ItemID)
Hi,
I think you want ItemID as dimension,
and in expression : max(MapPrice) and max(SalesPrice)
regards
Hi,
Something like:
Saludos
No Oliver - I have Item ID as a dimension already along with a lot of other expressions in my straight table. I need Map Price and Sales Price as additional dimensions in my straight table.
Thanks.
Use this as dimension
Aggr(Max(MapPrice), ItemID)
Aggr(Max(SalesPrice), ItemID)
Use calculated dimension like:
=Aggr(Max(MapPrice),ItemID)
=Aggr(Max(SalesPrice),ItemID)
I can't use Max(SalesPrice) - what if the data changes to something like this:
ItemID | FromDate | ToDate | MapPrice | PriceList | SalesPrice |
---|---|---|---|---|---|
00572-393 | 0 | 0 | 169.99 | 99999 | 475 |
00572-393 | 20170724 | 0 | 285 | 99999 | 268 |
00572-514 | 0 | 0 | 199.99 | 99999 | 475 |
00572-515 | 0 | 0 | 291 | 99999 | 485 |
00572-516 | 0 | 0 | 306 | 99999 | 510 |
00572-517 | 0 | 0 | 312 | 99999 | 520 |
00573-009 | 0 | 0 | 174.99 | 99999 | 250 |
00573-010 | 0 | 0 | 184.99 | 99999 | 264 |
00573-011 | 0 | 0 | 184.99 | 99999 | 264 |
00573-013 | 0 | 0 | 184.99 | 99999 | 264 |
00573-014 | 0 | 174.99 | 99999 | 250 | |
00573-016 | 0 | 0 | 174.99 | 99999 | 250 |
00573-017 | 0 | 0 | 174.99 | 99999 | 250 |
00573-022 | 0 | 0 | 184.99 | 99999 | 264 |
00573-022 | 20170725 | 0 | 279.99 | 99999 | 400 |
My expected output has to be:
ItemID | MapPrice | SalesPrice |
---|---|---|
00572-393 | 285 | 268 |
00572-514 | 199.99 | 475 |
00572-515 | 291 | 485 |
00572-516 | 306 | 510 |
00572-517 | 312 | 520 |
00573-009 | 174.99 | 250 |
00573-010 | 184.99 | 264 |
00573-011 | 184.99 | 264 |
00573-013 | 184.99 | 264 |
00573-014 | 174.99 | 250 |
00573-016 | 174.99 | 250 |
00573-017 | 174.99 | 250 |
00573-022 | 279.99 | 400 |
I can't use Max(SalesPrice) - what if the data changes to something like this:
ItemID | FromDate | ToDate | MapPrice | PriceList | SalesPrice |
---|---|---|---|---|---|
00572-393 | 0 | 0 | 169.99 | 99999 | 475 |
00572-393 | 20170724 | 0 | 285 | 99999 | 268 |
00572-514 | 0 | 0 | 199.99 | 99999 | 475 |
00572-515 | 0 | 0 | 291 | 99999 | 485 |
00572-516 | 0 | 0 | 306 | 99999 | 510 |
00572-517 | 0 | 0 | 312 | 99999 | 520 |
00573-009 | 0 | 0 | 174.99 | 99999 | 250 |
00573-010 | 0 | 0 | 184.99 | 99999 | 264 |
00573-011 | 0 | 0 | 184.99 | 99999 | 264 |
00573-013 | 0 | 0 | 184.99 | 99999 | 264 |
00573-014 | 0 | 174.99 | 99999 | 250 | |
00573-016 | 0 | 0 | 174.99 | 99999 | 250 |
00573-017 | 0 | 0 | 174.99 | 99999 | 250 |
00573-022 | 0 | 0 | 184.99 | 99999 | 264 |
00573-022 | 20170725 | 0 | 279.99 | 99999 | 400 |
My expected output has to be:
ItemID | MapPrice | SalesPrice |
---|---|---|
00572-393 | 285 | 268 |
00572-514 | 199.99 | 475 |
00572-515 | 291 | 485 |
00572-516 | 306 | 510 |
00572-517 | 312 | 520 |
00573-009 | 174.99 | 250 |
00573-010 | 184.99 | 264 |
00573-011 | 184.99 | 264 |
00573-013 | 184.99 | 264 |
00573-014 | 174.99 | 250 |
00573-016 | 174.99 | 250 |
00573-017 | 174.99 | 250 |
00573-022 | 279.99 | 400 |
Sorry about the confusion - I need the record which has max(mapPrice) and the corresponding Sales Price.
Thank you. Please refer to edited dummy data.
Try this
=Aggr(If(NoOfRows() - RowNo() + 1 = 1, MapPrice), ItemID, MapPrice)
=Aggr(If(NoOfRows() - RowNo() + 1 = 1, SalesPrice), ItemID, SalesPrice)