Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted

Re: How to bring these values as dimensions?

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
Highlighted
Specialist II
Specialist II

Re: How to bring these values as dimensions?

Hi,

I think you want ItemID as dimension,

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

regards

Highlighted
Partner
Partner

Re: How to bring these values as dimensions?

Hi,

Something like:

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

Saludos

Highlighted
Creator II
Creator II

Re: How to bring these values as dimensions?

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.

Highlighted

Re: How to bring these values as dimensions?

Use this as dimension

Aggr(Max(MapPrice), ItemID)

Aggr(Max(SalesPrice), ItemID)

Capture.PNG

Highlighted
MVP
MVP

Re: How to bring these values as dimensions?

Use calculated dimension like:

=Aggr(Max(MapPrice),ItemID)

=Aggr(Max(SalesPrice),ItemID)

Highlighted
Creator II
Creator II

Re: How to bring these values as dimensions?

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
Highlighted
Creator II
Creator II

Re: How to bring these values as dimensions?

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.

Highlighted
Creator II
Creator II

Re: How to bring these values as dimensions?

Thank you. Please refer to edited dummy data.

Highlighted

Re: How to bring these values as dimensions?

Try this

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

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

Capture.PNG