Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I started recently to use QlikView so I have some issues to adapt myself.
I would like to know how to create a field in the script which tell me if the line checked has the maximum date depending on a criterion.
I don't want a chart solution, my aim is to do a data treatment to extract an excel after.
Example:
Data:
Assessment Name | Team | Date | MaxDate |
Paris420 | HR | 10/05/2018 | yes |
Paris420 | Sales | 10/05/2018 | yes |
Paris420 | HR | 06/03/2018 | no |
Paris420 | Sales | 06/03/2018 | no |
Singapore387 | HR | 06/09/2017 | no |
Singapore387 | Sales | 06/09/2017 | no |
Singapore387 | HR | 12/05/2018 | yes |
Singapore387 | Sales | 12/05/2018 | yes |
Singapore387 | HR | 06/03/2018 | no |
Singapore387 | Sales | 06/03/2018 | no |
HK73 | HR | 15/04/2018 | yes |
HK73 | Sales | 15/04/2018 | yes |
HK73 | HR | 07/04/2018 | no |
HK73 | Sales | 07/04/2018 | no |
The column MaxDate is what I'm expecting :
If the date is the maximum for an Assessment Name, it returns "Yes", else "no".
Thank you for your help !
My bad, try this
Data:
LOAD * INLINE [
Assessment Name, Team, Date
Paris420, HR, 10/05/2018
Paris420, Sales, 10/05/2018
Paris420, HR, 06/03/2018
Paris420, Sales, 06/03/2018
Singapore387, HR, 06/09/2017
Singapore387, Sales, 06/09/2017
Singapore387, HR, 12/05/2018
Singapore387, Sales, 12/05/2018
Singapore387, HR, 06/03/2018
Singapore387, Sales, 06/03/2018
HK73, HR, 15/04/2018
HK73, Sales, 15/04/2018
HK73, HR, 07/04/2018
HK73, Sales, 07/04/2018
];
Left Join (Data)
LOAD [Assessment Name],
Max(Date) as Date,
'Yes' as MaxDate
Resident Data
Group By [Assessment Name];
FinalData:
NoConcatenate
LOAD [Assessment Name],
Team,
Date,
If(Len(Trim(MaxDate)) = 0, 'no', MaxDate) as MaxDate
Resident Data;
DROP Table Data;
May be this
Data:
LOAD [Assessment Name],
Team,
Date
FROM ...;
Left Join (Data)
LOAD [Assessment Name],
Max(Date) as Date',
'Yes' as MaxDate
Resident Data;
FinalData:
NoConcatenate
LOAD [Assessment Name],
Team,
Date,
If(Len(Trim(MaxDate)) = 0, 'no', MaxDate) as MaxDate
Resident Data;
DROP Table Data;
Hello,
Thanks for your help but it returns me an error :
I saw that there is a function called firstsortedvalue() could it be used for this usecase (to generate a new calculated field in the script)?
Thanks to you, community
My bad, try this
Data:
LOAD * INLINE [
Assessment Name, Team, Date
Paris420, HR, 10/05/2018
Paris420, Sales, 10/05/2018
Paris420, HR, 06/03/2018
Paris420, Sales, 06/03/2018
Singapore387, HR, 06/09/2017
Singapore387, Sales, 06/09/2017
Singapore387, HR, 12/05/2018
Singapore387, Sales, 12/05/2018
Singapore387, HR, 06/03/2018
Singapore387, Sales, 06/03/2018
HK73, HR, 15/04/2018
HK73, Sales, 15/04/2018
HK73, HR, 07/04/2018
HK73, Sales, 07/04/2018
];
Left Join (Data)
LOAD [Assessment Name],
Max(Date) as Date,
'Yes' as MaxDate
Resident Data
Group By [Assessment Name];
FinalData:
NoConcatenate
LOAD [Assessment Name],
Team,
Date,
If(Len(Trim(MaxDate)) = 0, 'no', MaxDate) as MaxDate
Resident Data;
DROP Table Data;
Thank you, it is well working !
Thanks Sunny for the idea. It's also worked for me. I just put my example here. It might be useful for someone else😃
TM_A:
QUALIFY *;
UNQUALIFY ApplicationId,[GroupId],ApplicationDateMin;
LOAD *
FROM table A;
Left join(TM_A)
Load
GroupId,
if(Len(Trim(GroupId))>0, Min(Date(TM_REQ.ApplicationDate, 'DD/MM/YYYY'))) as ApplicationDateMin
Resident TM_A
group by GroupId;
FinalData:
NoConcatenate
LOAD GroupId,
if(Len(Trim(GroupId))=0, Date(TM_REQ.ApplicationDateNew, 'DD/MM/YYYY'),ApplicationDateMin) as Application_CalendarDate
Resident TM_A;
Regards,
Panos