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)

1 Solution

Accepted Solutions
sunny_talwar

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

=Aggr(Max(MapPrice), ItemID)

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

View solution in original post

14 Replies
ogautier62
Specialist II
Specialist II

Hi,

I think you want ItemID as dimension,

and in expression : max(MapPrice) and max(SalesPrice)

regards

fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

Something like:

2018-05-29 13_26_04-QlikView - [C__Users_ES33517470F_Downloads_Item Prices as Dimensions.qvw_].png

Saludos

Anonymous
Not applicable
Author

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.

sunny_talwar

Use this as dimension

Aggr(Max(MapPrice), ItemID)

Aggr(Max(SalesPrice), ItemID)

Capture.PNG

tresesco
MVP
MVP

Use calculated dimension like:

=Aggr(Max(MapPrice),ItemID)

=Aggr(Max(SalesPrice),ItemID)

Anonymous
Not applicable
Author

I can't use Max(SalesPrice) - what if the data changes to something like this:

ItemIDFromDateToDateMapPricePriceListSalesPrice
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

My expected output has to be:

ItemIDMapPriceSalesPrice
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
Anonymous
Not applicable
Author

I can't use Max(SalesPrice) - what if the data changes to something like this:

ItemIDFromDateToDateMapPricePriceListSalesPrice
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

My expected output has to be:

ItemIDMapPriceSalesPrice
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

Re: How to bring these values as dimensions?

Sorry about the confusion - I need the record which has max(mapPrice) and the corresponding Sales Price.

Anonymous
Not applicable
Author

Thank you. Please refer to edited dummy data.

sunny_talwar

Try this

=Aggr(If(NoOfRows() - RowNo() + 1 = 1, MapPrice), ItemID, MapPrice)

=Aggr(If(NoOfRows() - RowNo() + 1 = 1, SalesPrice), ItemID, SalesPrice)

Capture.PNG