How to calculate the first day of a different status

[Arto_Kukkonen]

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

[Arthur_Fong · Accepted solution]

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

[Brett_Bleess]

See duplicate post for further information:

https://community.qlik.com/t5/QlikView-Scripting/How-to-calculate-the-first-day-of-a-different-statu...

Regards,
Brett

[Arto_Kukkonen]

Brilliant!  I really appreciate your help. Thank you!

[Saravanan_Desingh]

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;