Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! Please let me know is it possible to make to a script to find first day of StatusA and the fist day of StatusB and calculates the days between those.
Data is sort by CustomerID, TransactionData, Status
CustometID | TransactionDate | Status | StatusA | StatusB | QliView should calculate CountDays | |
8003132690 | 7.9.2015 | StatusA | 7.9.2015 | |||
8003132690 | 31.12.2015 | StatusA | 31.12.2015 | |||
8003132690 | 29.2.2016 | StatusA | 29.2.2016 | |||
8003132690 | 1.6.2016 | StatusA | 1.6.2016 | |||
8003132690 | 19.9.2016 | StatusB | 19.9.2016 | 378 | = 19.9.2016 - 7.9.2015 | |
8003132690 | 27.3.2017 | StatusB | 27.3.2017 | |||
8003132690 | 1.6.2017 | StatusA | 1.6.2017 | |||
8003132690 | 2.10.2017 | StatusA | 2.10.2017 | |||
8003132690 | 3.1.2018 | StatusA | 3.1.2018 | |||
8003132690 | 17.5.2018 | StatusA | 17.5.2018 | |||
8003132690 | 9.8.2018 | StatusA | 9.8.2018 | |||
8003132690 | 22.8.2018 | StatusB | 22.8.2018 | 447 | =22.8.2018 - 1.6.2017 | |
8003132690 | 25.9.2018 | StatusA | 25.9.2018 | |||
8003132690 | 18.3.2019 | StatusB | 18.3.2019 | 174 | =18.3.2019 - 25.9.2018 | |
8003132690 | 3.6.2019 | StatusA | 3.6.2019 | |||
8003132690 | 5.6.2019 | StatusB | 5.6.2019 | 2 | =5.6.2019 - 3.6.2019 | |
8003132690 | 6.6.2019 | StatusB | 6.6.2019 | |||
8003132690 | 6.8.2019 | StatusA | 6.8.2019 | |||
8003132690 | 19.8.2019 | StatusB | 19.8.2019 | 13 | =19.8.2019 - 6.8.2019 | |
1014 | Total_Days |
Arto, I am no expert here, but hopefully my post will get this back to the top of things and someone else with a bit more experience can provide some additional ideas on things. I believe you are going to need to use Order or Group by to get things sorted on the load, then you may need to use Peek to get the first value of each etc.
After that, just a matter of using the date functions to get the numeric value of each date and subtract those to get the delta I believe, but someone else may have something better here.
Here is another Community post with a solution on the difference calc:
I am pretty sure there is likely one on getting your first date from each status too, but that one was a bit more difficult to figure out what to search upon as far as key words. Sorry I do not have anything better, but hopefully this gives you a path forward.
Regards,
Brett
See duplicate post for further information:
Regards,
Brett
One solution is.
SET DateFormat='D.M.YYYY';
tab1:
LOAD RowNo() As RowID,*, If(Status<>Peek(Status),RangeSum(Peek(K1),1),Peek(K1)) As K1 INLINE [
CustometID, TransactionDate, Status
8003132690, 7.9.2015, StatusA
8003132690, 31.12.2015, StatusA
8003132690, 29.2.2016, StatusA
8003132690, 1.6.2016, StatusA
8003132690, 19.9.2016, StatusB
8003132690, 27.3.2017, StatusB
8003132690, 1.6.2017, StatusA
8003132690, 2.10.2017, StatusA
8003132690, 3.1.2018, StatusA
8003132690, 17.5.2018, StatusA
8003132690, 9.8.2018, StatusA
8003132690, 22.8.2018, StatusB
8003132690, 25.9.2018, StatusA
8003132690, 18.3.2019, StatusB
8003132690, 3.6.2019, StatusA
8003132690, 5.6.2019, StatusB
8003132690, 6.6.2019, StatusB
8003132690, 6.8.2019, StatusA
8003132690, 19.8.2019, StatusB
];
Left Join(tab1)
LOAD K1, Date(Min(TransactionDate)) As DateA
Resident tab1
Where Status='StatusA'
Group By K1
;
Left Join(tab1)
LOAD K1-1 As K1, Date(Min(TransactionDate)) As DateB
Resident tab1
Where Status='StatusB'
Group By K1
;
tab2:
LOAD *, DateB-DateA As CountDays
Resident tab1;
Drop Table tab1;