Help with FirstsortedValue

[#1]

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.

[malini_qlikview]

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;

[#3]

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.

[mgavidia]

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)

[#5]

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

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

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

hope this helps

regards

Marco

[#7]

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

Thanks

[Peter_Cammaert]

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

[#9]

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.

[pamaxeed]

Here you are

Cheers,

Patric