Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am new to QlikView.I'm trying to split a date field into 2 fields but I cannot do it. I searched on the community but could not find the solution.
I have the below table as input. Date format is DD-MM-YYYY.
Temp:
Load * Inline[
EmpId, Location, Date
1,Hyderabad,01-01-2017
2, Mumbai, 01-01-2017
1, Hyderabad, 01-12-2017
];
Now I need to divide the Date field into StartDate and EndDate based on EmpId field.
The required output should be like below.
EmpId | Location | StartDate | EndDate |
1 | Hyderabad | 01-01-2017 | 01-12-2017 |
2 | Mumbai | 01-01-2017 | - |
Please help me to solve this scenario.
Regards
Shashi
Hi Slashi,
See possible solution below:
Temp:
Load * Inline[
EmpId, Location, Date
1,Hyderabad,01-01-2017
2, Mumbai, 01-01-2017
1, Hyderabad, 01-12-2017
];
StartEndDate:
Left Join (Temp)
Load
EmpId,
StartDate,
If(StartDate <> EndDate, EndDate) AS EndDate;
Load
EmpId,
Date(Min(Date#(Date, 'MM-DD-YYYY')),'MM-DD-YYYY') AS StartDate,
Date(Max(Date#(Date, 'MM-DD-YYYY')),'MM-DD-YYYY') AS EndDate
Resident Temp
Group BY EmpId;
Thanks,
Vitalii
What if data like this?
Temp:
Load * Inline[
EmpId, Location, Date
1,Hyderabad,01-01-2017
2,Hyderabad,07-01-2017
2, Mumbai, 01-01-2017
2, Mumbai, 07-01-2017
2, Mumbai, 10-01-2017
1, Hyderabad, 01-12-2017
];
Hi Slashi,
See possible solution below:
Temp:
Load * Inline[
EmpId, Location, Date
1,Hyderabad,01-01-2017
2, Mumbai, 01-01-2017
1, Hyderabad, 01-12-2017
];
StartEndDate:
Left Join (Temp)
Load
EmpId,
StartDate,
If(StartDate <> EndDate, EndDate) AS EndDate;
Load
EmpId,
Date(Min(Date#(Date, 'MM-DD-YYYY')),'MM-DD-YYYY') AS StartDate,
Date(Max(Date#(Date, 'MM-DD-YYYY')),'MM-DD-YYYY') AS EndDate
Resident Temp
Group BY EmpId;
Thanks,
Vitalii
You can do this in the front-end of the application by adding a chart with EmpId & Location as dimensions and two expressions StartDate = min(Date) and EndDate = max(Date).
Or in the load script
Load
EmpId, Location, date#(Date, 'DD-MM-YYYY') as Date
Inline[
EmpId, Location, Date
1,Hyderabad,01-01-2017
2,Hyderabad,07-01-2017
2, Mumbai, 01-01-2017
2, Mumbai, 07-01-2017
2, Mumbai, 10-01-2017
1, Hyderabad, 01-12-2017
];
Data:
Load
EmpId,
Location,
date(min(Date)) as StartDate,
date(max(Date)) as EndDate
resident Temp
group by EmpId, Location
;
drop table Temp;
Hi Vitalii,
That is perfect, thank you. This worked great!
Regards
Shashi
Thanks Colin,
As per your instructions, I could do this in front-end.
Regards
Shashi