Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
Could you please help to get the below output as explained.
Please find the attached image for your reference.
So, in order to calculate the networkdays between ERD and Modified On,
1. the first row should pick the first values from both the columns, hence networkdays = 0
2. Networkdays = 1 where ERD is from the 2nd row i.e., 2nd Jan and Modified on from last loaded (or first row) i.e., 1st Jan
3. Networkdays = 1 where ERD is from the 3rd row i.e., 3rd Jan and Modified on from last loaded (or Second row) i.e., 2nd Jan
Data:
Load * Inline [
ERD,ERD Date, Modified On
ERD,01/01/2020 14:23:00,01/01/2020 14:27:00
ERD,02/01/2020 14:23:00,02/01/2020 10:27:00
ERD,03/01/2020 14:23:00,03/01/2020 10:27:00 ];
New:
NoConcatenate
Load *,
if(RowNo()=1,Floor([ERD Date])-floor([Modified On]),floor([ERD Date])-Previous(floor([Modified On]))) as NetWorkDays
Resident Data
Order by [ERD Date];
Drop Table Data;
Data:
Load * Inline [
ERD,ERD Date, Modified On
ERD,01/01/2020 14:23:00,01/01/2020 14:27:00
ERD,02/01/2020 14:23:00,02/01/2020 10:27:00
ERD,03/01/2020 14:23:00,03/01/2020 10:27:00 ];
New:
NoConcatenate
Load *,
if(RowNo()=1,Floor([ERD Date])-floor([Modified On]),floor([ERD Date])-Previous(floor([Modified On]))) as NetWorkDays
Resident Data
Order by [ERD Date];
Drop Table Data;
Did Kush's post get you what you needed? If so, do not forget to return to your thread and use the Accept as Solution button on his post to give him credit for the help and to let other Community Members know that worked. If you still need further assistance, please leave an update post.
Regards,
Brett