Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

danimelo1
New Contributor III

Problem with groupby and firstsortedvalue

Hello All,

I have the following table:

   

Person IDStart DateEnd DateStatus
000000014/30/200812/31/99990
000000014/1/201312/31/99993
000000014/1/201412/31/99993
000000014/1/201412/31/99993
000000014/1/201312/31/99990
000000028/1/200512/31/99993
000000034/1/200912/31/99990
000000034/1/200912/31/99993
0000000310/17/201412/31/99993
000000046/1/201212/31/99993
000000055/1/20144/19/20163
000000054/20/201612/31/99993
000000054/20/201612/31/99993
000000067/31/200812/31/99990
0000000610/1/201412/31/99993
000000075/1/201412/31/99993
000000074/30/201012/31/99990
000000076/1/201012/31/99990
0000000710/1/200712/31/99990
0000000711/30/200812/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.

foto.jpg

Any idea?

Thanks

1 Solution

Accepted Solutions
danimelo1
New Contributor III

Re: Problem with groupby and firstsortedvalue

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

1 Reply
danimelo1
New Contributor III

Re: Problem with groupby and firstsortedvalue

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