Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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];