Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to bring these values as dimensions?

Hi Qlik Experts,

I have the following dummy data:

**EDITED DUMMY DATA FOR INCLUDING ANOTHER SCENARIO*

ItemID FromDate ToDate MapPrice PriceList SalesPrice
00572-39300169.9999999475
00572-39320170724028599999268
00572-51400199.9999999475
00572-5150029199999485
00572-5160030699999510
00572-5170031299999520
00573-00900174.9999999250
00573-01000184.9999999264
00573-01100184.9999999264
00573-01300184.9999999264
00573-0140174.9999999250
00573-01600174.9999999250
00573-01700174.9999999250
00573-02200184.9999999264
00573-022201707250279.9999999400

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-393285268
00572-514199.99475
00572-515291485
00572-516306510
00572-517312520
00573-009174.99250
00573-010184.99264
00573-011184.99264
00573-013184.99264
00573-014174.99250
00573-016174.99250
00573-017174.99250
00573-022279.99400

I'm having a hard time achieving this - any help appreciated

Regards,

Benazir

(PFA the dummy QVW)

14 Replies
Anonymous
Not applicable
Author

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

sunny_talwar

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

Capture.PNG

Anonymous
Not applicable
Author

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.

sunny_talwar

My bad, I mis-read your requirement... try this

=Aggr(Max(MapPrice), ItemID)

=Aggr(FirstSortedValue(SalesPrice, -MapPrice), ItemID)

Anonymous
Not applicable
Author

Fabulous!! This works like a charm.

You're a life saver Sunny Thanks a ton!

Regards,

Benazir