topic Re: Problem with groupby and firstsortedvalue in App Development

Problem with groupby and firstsortedvalue

danimelo1 — Tue, 28 Nov 2017 16:38:08 GMT

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

Re: Problem with groupby and firstsortedvalue

danimelo1 — Tue, 28 Nov 2017 17:09:35 GMT

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];