Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I want to figure out a way to calculate difference between dates columns below.
Here is my Data set below-
Number Id | Loop Number: | FOT Date | Loop Date | Status |
160030-0006 | TL1 | 7/26/2019 | 8/16/2019 | Open |
160030-0006 | TL2 | 9/16/2019 | 10/16/2019 | Open |
160030-0006 | TL3 | 10/26/2019 | 11/25/2019 | Open |
160030-0006 | TL4 | 12/5/2019 | 1/4/2020 | Closed |
206699-4001 | TL1 | 1/14/2020 | 2/13/2020 | Open |
206699-4001 | TL2 | 2/15/2020 | 2/16/2020 | Open |
206699-4001 | TL3 | 2/20/2020 | 2/22/2020 | Closed |
I want to calculate No of days between Loop Date and FOT Date for each Number Id when there Status are Open only.
Example: For a Number Id below, TL1 is the first loop so Count of Days will be dash, For TL2: the count of days is calculated by the logic
TL2= Loop Date of (TL1) - FOT Date of (TL2)
TL3=Loop date of(TL2) - FOT Date of (TL2)
Number Id | Loop Number: | FOT Date | Loop Date | Status | Count of Days |
206699-4001 | TL1 | 1/14/2020 | 2/13/2020 | Open | - |
206699-4001 | TL2 | 2/15/2020 | 2/16/2020 | Open | 2 |
206699-4001 | TL3 | 2/20/2020 | 2/22/2020 | Closed | 4 |
I need the count of days like these:-
TL1 | 2 Days Difference | TL2 | 4 days Difference | TL3 |
Note :
Not sure how to proceed !
Please let me know if you have question.
Thanks in advance
Load your raw data into a table, for example named "Data". Then update the resident table like this:
Left Join(Data)
LOAD
[Number Id],
[Loop Number:],
if([Number Id] = Previous([Number Id]),
[FOT Date] - Previous([Loop Date]),
'-') as Duration
Resident Data
Order By [Number Id], [Loop Number:];
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Thanks Rob !
But I tried the same scripts which you shared, its not working properly.
kindly see the output below -
Number ID | Loop Number: | FOT Date | Loop Date | Status | Duration |
160030-0006 | TL1 | 7/26/2019 | 8/16/2019 | Open | |
160030-0006 | TL2 | 9/16/2019 | 10/16/2019 | Open | 30 |
160030-0006 | TL3 | 10/26/2019 | 11/25/2019 | Open | |
160030-0006 | TL4 | 12/5/2019 | 1/4/2020 | Closed | |
206699-4001 | TL1 | 1/14/2020 | 2/13/2020 | Open | 30 |
206699-4001 | TL2 | 1/30/2019 | 3/11/2019 | Open | - |
206699-4001 | TL3 | 2/25/2019 | 2/28/2019 | Closed | - |
348167004 | TL1 | 1/18/2019 | 2/7/2019 | Open | - |
348167004 | TL2 | 2/14/2019 | 2/17/2019 | Open | - |
348167005 | TL3 | 2/20/2019 | 2/23/2019 | Open | - |
348167006 | TL4 | 3/5/2019 | 3/8/2019 | Closed | - |
There are dashes in last 6 rows of Duration's Column and for the number id = 160030-0006 in above table, In loop number TL3, it should display Loop date(TL2) 10/16/2019 - FOT date (TL3) 10/26/2019 = 10 days
logic for duration should be Loop Date(T1) - FOT Date(T2) ...so on!
Also for the first appearance of Number ID it should be empty or dash.
Please let me know if you need additional information.
Thanks Rob!
Can you please post your script?
-Rob