Dates help in backend

[mahitham]

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

[sunny_talwar · Accepted solution]

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

];

[mahitham]

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

];

[sunny_talwar]

Just in days? You can do

Floor(Startdate - Enddate) as DiffDays

[surendraj]

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

[kingsleyh]

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

[nsetty]

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
];