topic Re: First and second highest by product in one row in QlikView

First and second highest by product in one row

pavandbs — Sun, 26 Nov 2017 15:15:13 GMT

Hello Experts,

Please help to write a set expression for the below output

For each product show in one row

1.Latest Date

2. Latest Purchase Unit

3. Latest Invoice vendor

4. 2nd Latest Date

5. Purchase Unit for the 2nd latest date

6. Invoice Vendor for the 2nd latest date

In one the two lines highlighted in green should show as one row in output by product.

Please note some products may contain null date, invoice vendor also

Re: First and second highest by product in one row

Anil_Babu_Samineni — Sun, 26 Nov 2017 15:17:51 GMT

Please attach Excel file?

Re: First and second highest by product in one row

pavandbs — Sun, 26 Nov 2017 15:21:08 GMT

Attached for your reference.

Re: First and second highest by product in one row

Anil_Babu_Samineni — Sun, 26 Nov 2017 15:31:56 GMT

PFA

Re: First and second highest by product in one row

pavandbs — Sun, 26 Nov 2017 15:47:25 GMT

Hi Anil,

Thanks for your reply this works fine if I select only one product. Attaching actual QVW which having multiple products.

Re: First and second highest by product in one row

Anil_Babu_Samineni — Sun, 26 Nov 2017 16:35:11 GMT

V1 Attached

Re: First and second highest by product in one row

pavandbs — Sun, 26 Nov 2017 16:50:58 GMT

Hi Anil,

Still no luck when I replicate your expressions in my original document. Can you try the same on my very first comment.

Thanks,

Pavan Ch

Re: First and second highest by product in one row

Anil_Babu_Samineni — Tue, 28 Nov 2017 02:00:49 GMT

Just to understand, Can we discuss use case? If we select one product which you known the associate answer then what you are going to have to see?

Re: First and second highest by product in one row

pavandbs — Fri, 01 Dec 2017 06:31:05 GMT

Hi Anil,

I did not quite get you, basically if you open my Probolem 3.qvw the solution you provided still not working. It works if I restrict selection to only one product. If I do clear its not working. In problem 3 .qvw first sheet image is the expected out.

Re: First and second highest by product in one row

sunny_talwar — Fri, 01 Dec 2017 06:43:53 GMT

May be check the attached

Re: First and second highest by product in one row

pavandbs — Fri, 01 Dec 2017 06:49:27 GMT

Hi Sunny,

Glad that you messaged. I have tried hard to get the expected output literally spent 2 days. Got struck with 2nd highest values to pick. Greatly appreciate if you could help on this solution. I guess some null values causing the issue.

Thanks,

Pavan

Re: First and second highest by product in one row

sunny_talwar — Fri, 01 Dec 2017 06:52:03 GMT

You mentioned that you wanted to get the same output as the image... what is the issue here? Nulls seems to be because of non-existance of 2nd latest date... do you know a case where you know there is a 2nd latest date, but you don't see it on the chart?

Re: First and second highest by product in one row

pavandbs — Fri, 01 Dec 2017 06:52:15 GMT

Sorry I didnt see the attached. Let me check and get back to you

Thanks,

Pavan Ch

Re: First and second highest by product in one row

pavandbs — Fri, 01 Dec 2017 06:55:22 GMT

awesome. Thanks a lot.