Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Im new here,
I have below data in a MySQL DataBase. I need to show only the max date for a Uniq Value in a row for a straight table in Qlik Sense.
Can anyone please help / point me in the right direction. I have generated MAX(DATE) but need to remove the rows with the same UniqID so I only have one UniqID and one row.
INPUT:
UniqID | Name | Service Type | ExpiryDate | MAX DATE | Consult Hours |
---|---|---|---|---|---|
Ad102 | ADAM | IT | 21/10/2014 16:58 | 21/10/2014 16:58 | 7 |
Ad102 | ADAM | Programming | 12/10/2012 08:50 | 21/10/2014 16:58 | 3 |
Ad102 | ADAM | IT | 09/09/2011 11:11 | 21/10/2014 16:58 | 2 |
Bs899 | Ben | Programming | 09/02/2010 12:58 | 01/03/2016 09:11 | 2 |
Bs899 | Ben | Programming | 01/03/2016 09:11 | 01/03/2016 09:11 | 5 |
I need the following in the OutPut:
UniqID | Name | Service Type | ExpiryDate | MAX DATE | Consult Hours |
---|---|---|---|---|---|
Ad102 | ADAM | IT | 21/10/2014 16:58 | 21/10/2014 16:58 | 7 |
Bs899 | Ben | Programming | 01/03/2016 09:11 | 01/03/2016 09:11 | 5 |
Hope it makes sense
Use UniqID, and Name as dimensions and the following expressions:
Max(ExpiryDate)
FirstSortedValue([Service Type ].-ExpiryDate)
FirstSortedValue([Consult Hours],-ExpiryDate)
May be like this:
Dimension
1) UniqID
2) Name
Expressions:
1) FirstSortedValue(DISTINCT [Service Type], -[Consult Hours])
2) TimeStamp(FirstSortedValue(DISTINCT ExpiryDate, -[Consult Hours]))
3) TimeStamp(FirstSortedValue(DISTINCT [MAX DATE], -[Consult Hours]))
4) Max([Consult Hours])