Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! Please let me know what kind of loop? I have to make to calculate Count_Days value in script. Always the first Status_A date value and the first Status_B date value should pic up and calculate the days amount.
Customer | Transaction_date | Status_A | Status_B | Count_Days | |
8003132690 | 7.9.2015 | 7.9.2015 | |||
8003132690 | 31.12.2015 | 31.12.2015 | |||
8003132690 | 29.2.2016 | 29.2.2016 | |||
8003132690 | 1.6.2016 | 1.6.2016 | |||
8003132690 | 19.9.2016 | 19.9.2016 | 378 | = 19.9.2016 - 7.9.2015 | |
8003132690 | 27.3.2017 | 27.3.2017 | |||
8003132690 | 1.6.2017 | 1.6.2017 | |||
8003132690 | 2.10.2017 | 2.10.2017 | |||
8003132690 | 3.1.2018 | 3.1.2018 | |||
8003132690 | 17.5.2018 | 17.5.2018 | |||
8003132690 | 9.8.2018 | 9.8.2018 | |||
8003132690 | 22.8.2018 | 22.8.2018 | 447 | =22.8.2018 - 1.6.2017 | |
8003132690 | 25.9.2018 | 25.9.2018 | |||
8003132690 | 18.3.2019 | 18.3.2019 | 174 | =18.3.2019 - 25.9.2018 | |
8003132690 | 3.6.2019 | 3.6.2019 | |||
8003132690 | 5.6.2019 | 5.6.2019 | 2 | =5.6.2019 - 3.6.2019 | |
8003132690 | 6.6.2019 | 6.6.2019 | |||
8003132690 | 6.8.2019 | 6.8.2019 | |||
8003132690 | 19.8.2019 | 19.8.2019 | 13 | =19.8.2019 - 6.8.2019 | |
1014 | Total_days |
There you go:
Use min() function to get the minimum Date of status A based on the flags created using peek function.
Refer logic in qvw attached.
Thanks and regards,
Arthur Fong
There you go:
Use min() function to get the minimum Date of status A based on the flags created using peek function.
Refer logic in qvw attached.
Thanks and regards,
Arthur Fong
See duplicate post for further information:
Regards,
Brett
Brilliant! I really appreciate your help. Thank you!
One solutions.
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;