Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
Can any one please help me on below requirement.
I have the source like below
Source:
Load * Inline [
App,ID,Startdate,Enddate
Banking,4586A,05/10/2018 00:00:00,08/10/2018
Crime Analytics,5793B,25/07/2018 00:00:00,
Crime Analytics,8794B,13/07/2018 00:00:00,
HR,48632E,12/09/2018 00:00:00,13/09/2018
HR,58632E,05/08/2016 00:00:00,
HR,68632E,09/06/2018 00:00:00,
Service,8632O,03/09/2018 00:00:00, 06/09/2018
Service,86321,01/10/2018 00:00:00, 04/10/2018
];
In the below input we have 8 rows. Here I need to keep only the rows where both startdate and enddate is not blank by using backend script.
Please help me on this.
Please find the below attached app.
Thanks in advance
Input
Expected output
Try this
Load *
Where Len(Trim(Startdate)) > 0 and Len(Trim(Enddate)) > 0;
Load * Inline [
App,ID,Startdate,Enddate
Banking,4586A,05/10/2018 00:00:00,08/10/2018
Crime Analytics,5793B,25/07/2018 00:00:00,
Crime Analytics,8794B,13/07/2018 00:00:00,
HR,48632E,12/09/2018 00:00:00,13/09/2018
HR,58632E,05/08/2016 00:00:00,
HR,68632E,09/06/2018 00:00:00,
Service,8632O,03/09/2018 00:00:00, 06/09/2018
Service,86321,01/10/2018 00:00:00, 04/10/2018
];
Try this
Load *
Where Len(Trim(Startdate)) > 0 and Len(Trim(Enddate)) > 0;
Load * Inline [
App,ID,Startdate,Enddate
Banking,4586A,05/10/2018 00:00:00,08/10/2018
Crime Analytics,5793B,25/07/2018 00:00:00,
Crime Analytics,8794B,13/07/2018 00:00:00,
HR,48632E,12/09/2018 00:00:00,13/09/2018
HR,58632E,05/08/2016 00:00:00,
HR,68632E,09/06/2018 00:00:00,
Service,8632O,03/09/2018 00:00:00, 06/09/2018
Service,86321,01/10/2018 00:00:00, 04/10/2018
];
Hi Sunny,
Thanks for your help.
I need one more small help here from start date and End date please help me to calculate the difference in days.
Here start date is in time stamp format and enddate is in DD/MM/YYYY format
Load * Inline [
App,ID,Startdate,Enddate
Banking,4586A,05/10/2018 00:24:00,08/10/2018
];
Just in days? You can do
Floor(Startdate - Enddate) as DiffDays
Try like...
Load * ,interval(date(date#(Startdate,'DD/MM/YYYY HH:MM:SS'),'DD/MM/YYYY'),Enddate,'dd') as difference;
Load * Inline [
App,ID,Startdate,Enddate
Banking,4586A,05/10/2018 00:24:00,08/10/2018
];
--Surendra
Hi Sunny.
Unfortunately that won't work as those fields aren't recognized as dates yet.
Please add this to your preceding load:
DATE#(Enddate, 'DD/MM/YYYY')-DATE#(Startdate,'DD/MM/YYYY hh:mm:ss') as NumOfDays
Also it will be End - Start instead of Start-End
Many thanks
Kingsley
Can you try this script
DTTable:
Load *
, Enddate-Startdate AS Diff;
Load App,ID, DATE(Timestamp#(Startdate, 'DD/MM/YYYY hh:mm:ss'), 'MM/DD/YYYY') AS Startdate, DATE(DATE#(Enddate, 'DD/MM/YYYY'), 'MM/DD/YYYY') AS Enddate
Where Len(Trim(Startdate)) > 0 and Len(Trim(Enddate)) > 0;
Load * Inline [
App,ID,Startdate,Enddate
Banking,4586A,05/10/2018 00:00:00,08/10/2018
Crime Analytics,5793B,25/07/2018 00:00:00,
Crime Analytics,8794B,13/07/2018 00:00:00,
HR,48632E,12/09/2018 00:00:00,13/09/2018
HR,58632E,05/08/2016 00:00:00,
HR,68632E,09/06/2018 00:00:00,
Service,8632O,03/09/2018 00:00:00, 06/09/2018
Service,86321,01/10/2018 00:00:00, 04/10/2018
];