Problem with groupby and firstsortedvalue

[danimelo1]

Hello All,

I have the following table:

Person ID	Start Date	End Date	Status
00000001	4/30/2008	12/31/9999	0
00000001	4/1/2013	12/31/9999	3
00000001	4/1/2014	12/31/9999	3
00000001	4/1/2014	12/31/9999	3
00000001	4/1/2013	12/31/9999	0
00000002	8/1/2005	12/31/9999	3
00000003	4/1/2009	12/31/9999	0
00000003	4/1/2009	12/31/9999	3
00000003	10/17/2014	12/31/9999	3
00000004	6/1/2012	12/31/9999	3
00000005	5/1/2014	4/19/2016	3
00000005	4/20/2016	12/31/9999	3
00000005	4/20/2016	12/31/9999	3
00000006	7/31/2008	12/31/9999	0
00000006	10/1/2014	12/31/9999	3
00000007	5/1/2014	12/31/9999	3
00000007	4/30/2010	12/31/9999	0
00000007	6/1/2010	12/31/9999	0
00000007	10/1/2007	12/31/9999	0
00000007	11/30/2008	12/31/9999	0

Table:

LOAD

    "Person ID",

//     Pers.No.,

    Date("Start Date") as "Start Date",

     "End Date",

     Status    

FROM [myFile.xlsx]

(ooxml, embedded labels, table is Sheet8);

Resul_Tmp:

LOAD

[Person ID] as Person,

FirstSortedValue(Status,-[Start Date],1) as Status_Tmp

Resident Table

Group By [Person ID];

Then I get this null values and I cannot understand why.

Any idea?

Thanks

[danimelo1 · Accepted solution]

Solved! thanks to this: FirstSortedValue issue | Qlik Community

I just added a distinct after the function FirstSortedValue

Table:

LOAD

    "Person ID",

//     Pers.No.,

    Date("Start Date") as "Start Date",

     "End Date",

     Status    

FROM [lib://qlikid_danielmeloinn/Prueba.xlsx]

(ooxml, embedded labels, table is Sheet8);

Resul_Tmp:

LOAD

[Person ID] as Person,

FirstSortedValue(distinct Status,-[Start Date],1) as Status_Tmp

Resident Table

Group By [Person ID];