How to calculate the first day of a different status

[Arto_Kukkonen]

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

[Brett_Bleess]

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.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

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:

https://community.qlik.com/t5/QlikView-Creating-Analytics/Number-of-days-between-dates/m-p/328986#M1...

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

[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

[Saravanan_Desingh]

[Saravanan_Desingh]

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;