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)
Hi Sunny,
I tried this and it works fine only if there are 2 duplicates. If there are more than 2 records for a given Item ID, then this isn't working.
Regards,
Benazir
Would you be able to share a sample where this isn't working? Because for me it is working... I added another row for 00572-393
Look closely Sunny - for the first Item ID it reflect 6 and 7 as MAP and sales prices. It should ideally display max of map price - 285 and corresponding sales price 268.
My bad, I mis-read your requirement... try this
=Aggr(Max(MapPrice), ItemID)
=Aggr(FirstSortedValue(SalesPrice, -MapPrice), ItemID)
Fabulous!! This works like a charm.
You're a life saver Sunny Thanks a ton!
Regards,
Benazir