tangled with aggr, firstSortedValue ...

demienyu — Wed, 31 Jan 2018 09:00:40 GMT

Need some help with expression.

I have a dataset of vendor price list in a form of following:

ItemId, Vendor, PriceDate, Price (whole dataset see below)

My app has one sheet and one table chart containing these four dimensions and some measures. They work fine.

I'm having a headake trying to make a last needed expression.

To define the best vendor price I need first to leave only records with Price > 0.

Then I sholud define last PriceDate by ItemId and Vendor.

Having done this I need to take min Price from all vendor prices and this Price is what i was looking for.

Of course I have to show in chart name of Vendor and PriceDate.

The problem is to make an expression showing PriceDate of the minimal vendor price, descripbed above.

In my test case the correct answer is:

ItemId

Vendor

TransDate

Price

A105

Orange Inc

2017-12-14

5.6

B230

Little U

2017-11-11

29.9

The dataset:

Prices:

LOAD * INLINE [ItemId, Vendor, PriceDate, Price

'A105','Little U',2017-01-07,0

'B230','Little U',2017-11-11,29.9

'A105','Orange Inc',2017-02-01,6.6

'A105','Orange Inc',2017-03-02,7.5

'A105','Orange Inc',2017-03-13,4.4

'A105','Little U',2017-03-31,10.2

'A105','Little U',2017-08-09,5.1

'B230','Orange Inc',2017-04-16,0

'B230','Orange Inc',2017-05-05,31.1

'B230','Orange Inc',2017-05-08,40.2

'A105','Orange Inc',2017-09-17,5.5

'A105','Orange Inc',2017-10-11,4.9

'B230','Orange Inc',2017-08-07,30.5

'B230','Orange Inc',2017-08-15,35.5

'A105','Little U',2017-10-18,0

'B230','Little U',2017-05-08,0

'B230','Little U',2017-10-24,35.6

'B230','Little U',2017-06-30,0

'B230','Orange Inc',2017-09-19,35.5

'B230','Little U',2017-09-01,33.6

'A105','Orange Inc',2017-12-14,5.6

'B230','Orange Inc',2017-11-03,39.8

'B230','Little U',2017-09-25,0

'B230','Little U',2017-05-17,38.1

'B230','Orange Inc',2017-11-09,32.4

'A105','Little U',2017-12-25,11.5

'B230','Little U',2017-04-10,14.8

'B230','Orange Inc',2017-12-16,0

];

Re: tangled with aggr, firstSortedValue ...

sunny_talwar — Wed, 31 Jan 2018 16:43:49 GMT

Check the attached

Re: tangled with aggr, firstSortedValue ...

demienyu — Thu, 01 Feb 2018 11:00:28 GMT

If I remove Vendor from table qlik leaves only record with ItemId = B230

This is not correct. Both items should stay and result (Price and Date) should be the same.

I understand that it is quite a different task, but...

Anyway, what is the solution?

Re: tangled with aggr, firstSortedValue ...

sunny_talwar — Thu, 01 Feb 2018 17:22:54 GMT

Check now

topic Re: tangled with aggr, firstSortedValue ... in App Development

tangled with aggr, firstSortedValue ...

Re: tangled with aggr, firstSortedValue ...

Re: tangled with aggr, firstSortedValue ...

Re: tangled with aggr, firstSortedValue ...