topic Re: Firstsorted value in QlikView

Firstsorted value

Anonymous — Mon, 11 Sep 2017 09:07:51 GMT

Date	product	max days	Number
07/21/2017	7700550007000100	3	654582-002
07/21/2017	7700550007000100	3	729871-001
07/21/2017	7700550007000100	3	773911-002
07/21/2017	7700550007000100	3	777321-001
07/22/2017	7700550007000100	3	654582-002
07/22/2017	7700550007000100	3	729871-001
07/22/2017	7700550007000100	3	773911-002
07/22/2017	7700550007000100	3	777321-001
07/23/2017	7700550007000100	3	654582-002
07/23/2017	7700550007000100	3	729871-001
07/23/2017	7700550007000100	3	773911-002
07/23/2017	7700550007000100	3	777321-001

4 numbers are blocking each date and days also same but i want output below one

654582-002,729871-001, 773911-002, 777321-001 in script level

Re: Firstsorted value

sunny_talwar — Mon, 11 Sep 2017 15:48:46 GMT

Those numbers with which date? 7/23/2017 or 7/21/2017? If it is 7/23... you can try this

Fact:

LOAD Date,

Product,

[max days],

Number

FROM ....;

Right Join (Fact)

LOAD Number,

Max(Date) as Date

Resident Fact

Group By Number;

Re: Firstsorted value

Peter_Cammaert — Mon, 11 Sep 2017 15:49:09 GMT

Can you post an example (in a table) of the expected output, based on your example data?

Re: Firstsorted value

Anil_Babu_Samineni — Mon, 11 Sep 2017 16:11:25 GMT

May be this? I know, This is weird solution but result may give

SET DateFormat='MM/DD/YYYY';

And expression this?

Only({<Date = {'$(=Min(Date))'}>} Number)

And As Sunny Suggested, I believe Min(Date) can sort rather Max(Date)

Re: Firstsorted value

its_anandrjs — Mon, 11 Sep 2017 19:05:51 GMT

In Script part try this ways

Data:

LOAD Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date,product, [max days],Number Inline

[

Date, product, max days, Number

07/21/2017, 7700550007000100, 3, 654582-002

07/21/2017, 7700550007000100, 3 ,729871-001

07/21/2017, 7700550007000100, 3 ,773911-002

07/21/2017, 7700550007000100, 3, 777321-001

07/22/2017, 7700550007000100, 3, 654582-002

07/22/2017, 7700550007000100, 3, 729871-001

07/22/2017, 7700550007000100, 3, 773911-002

07/22/2017, 7700550007000100, 3, 777321-001

07/23/2017, 7700550007000100, 3, 654582-002

07/23/2017, 7700550007000100, 3, 729871-001

07/23/2017, 7700550007000100, 3, 773911-002

07/23/2017, 7700550007000100, 3, 777321-001

];

NewTable:

LOAD

Date,product,[max days],

Concat( Number,',') as New

Resident Data Group By Date, product, [max days];

DROP Table Data;

Output

Re: Firstsorted value

vishsaggi — Mon, 11 Sep 2017 19:27:07 GMT

Hello Anand, Did you put the output from a different qvw? Does not match the data in the LOAD statement. Just FYI.

Re: Firstsorted value

its_anandrjs — Mon, 11 Sep 2017 19:34:29 GMT

Thanks i change this now please check earlier i was trying to update but it not get update now it is changed.

Re: Firstsorted value

vishsaggi — Mon, 11 Sep 2017 20:06:11 GMT

Thumbs Up!!