topic Re: Help with FirstsortedValue in QlikView

Help with FirstsortedValue

Thu, 24 Sep 2015 16:07:04 GMT

Group	ID	Code	date
80092905	92307669	D	2012
80092905	92307698	C	2013
80092905	99176178	D	2014
83087927	94345436	D	2013
88573862	92307687	D	2013
88573862	99176179	D	2014
89045464	99801874	D	2014
90407272	101747436	D	2014

I am trying to count the IDs for every Group which hit a code D first based on the date filed. I should get the count 5. Thanks for the help.

Re: Help with FirstsortedValue

malini_qlikview — Thu, 24 Sep 2015 16:41:20 GMT

Try the below,

Find the first date field in every group for code D and join it with the whole data set to get the result

Test:

Load

Inline

[

Group,ID,Code,date

80092905,92307669,D,2012

80092905,92307698,C,2013

80092905,99176178,D,2014

83087927,94345436,D,2013

88573862,92307687,D,2013

88573862,99176179,D,2014

89045464,99801874,D,2014

90407272,101747436,D,2014

];

Inner Join

Load

Group,

Min(date) AS date

Resident Test

where Code='D'

group by Group;

Re: Help with FirstsortedValue

Thu, 24 Sep 2015 16:43:28 GMT

Thanks for your response. But,I can not do it while loading as I have other tables in the appln. I am looking for something at the expression level.

Thanks.

Re: Help with FirstsortedValue

mgavidia — Thu, 24 Sep 2015 17:39:21 GMT

I am not sure if I understand correctly but anyhow...

- Straight table with Group as Dimension

- Expressions:
CODE: FirstSortedValue(Code,date)

DATE: FirstSortedValue(date,date)

Re: Help with FirstsortedValue

Thu, 24 Sep 2015 17:52:13 GMT

I want to count only the Ds. Lets say in the example I gave, There is a code A in 2011. I am looking for something like

Count(aggr(firstsortedvalue( {$<Code={'D'}>} ID,-Date),Group))

Group	ID	Code	date
80092905	96532532	A	2011
80092905	92307669	D	2012
80092905	92307698	C	2013
80092905	99176178	D	2014
83087927	94345436	D	2013
88573862	92307687	D	2013
88573862	99176179	D	2014
89045464	99801874	D	2014
90407272	101747436	D	2014

Thanks

Re: Help with FirstsortedValue

MarcoWedel — Thu, 24 Sep 2015 22:15:14 GMT

Hi,

one solution might be:

=Count(Aggr(If(FirstSortedValue(Code,date)='D',Group),Group))

hope this helps

regards

Marco

Re: Help with FirstsortedValue

Fri, 25 Sep 2015 14:53:10 GMT

The count should be 5. Not sure where the issue is.

Thanks

Re: Help with FirstsortedValue

Peter_Cammaert — Fri, 25 Sep 2015 15:04:33 GMT

Marco's example uses different data to show you that it works in all cases. He first sorts the records per group by date and only then checks whether the Code = D. For the first group, this results in Code = A (date = 2011), unlike in your data where there is no year 2011.

Just FYI. Best, Peter

Re: Help with FirstsortedValue

Fri, 25 Sep 2015 15:21:00 GMT

I am looking for something like this.

Group	2012	2013	2014
80092905	1
83087927		1	-
88573862		1	-
89045464		-	1
90407272	-		1

Sorry for the confusion.

Re: Help with FirstsortedValue

pamaxeed — Fri, 25 Sep 2015 16:59:11 GMT

Here you are

Cheers,

Patric