Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
danimelo1
Contributor

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
Contributor

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

View solution in original post

1 Reply
danimelo1
Contributor

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

View solution in original post