topic The internal logic values are in the script in QlikView

The internal logic values are in the script

wood — Sun, 22 Nov 2020 08:21:22 GMT

Hello, look at the following table. How to automatically generate the START DATE and END DATE fields in the script?

The start and end dates are taken from RUN_DATE as the contract number and audit result change.

Contract NO.	Run_Date	Audit Result	START DATE	END DATE
3700000059598	2020/11/21	Normal
3700000059598	2020/11/22	Normal
3700000059598	2020/11/22	Normal	2020/11/21	2020/11/22
3700000059598	2020/11/23	Update Location
3700000059598	2020/11/24	Update Location	2020/11/23	2020/11/24
3700000059598	2020/11/25	Normal
3700000059598	2020/11/26	Normal	2020/11/25	2020/11/26
3700000055555	2020/11/21	Normal
3700000055555	2020/11/22	Normal	2020/11/21	2020/11/22

Re: The internal logic values are in the script

Taoufiq_Zarra — Sun, 22 Nov 2020 11:33:18 GMT

@wood May be this :

Data: LOAD *,if(rowno()=1,1,if(peek([Contract NO.])=[Contract NO.] and [Audit Result]=peek([Audit Result]),peek(Idtmp),peek(Idtmp)+1)) as Idtmp INLINE [ Contract NO., Run_Date, Audit Result 3700000059598, 2020/11/21, Normal 3700000059598, 2020/11/22, Normal 3700000059598, 2020/11/22, Normal 3700000059598, 2020/11/23, Update Location 3700000059598, 2020/11/24, Update Location 3700000059598, 2020/11/25, Normal 3700000059598, 2020/11/26, Normal 3700000055555, 2020/11/21, Normal 3700000055555, 2020/11/22, Normal ]; output: noconcatenate load * resident Data; left join load [Contract NO.],[Audit Result],Idtmp,Date(Min(Date#(Run_Date,'YYYY/MM/DD'))) as [START DATE],Date(Max(Date#(Run_Date,'YYYY/MM/DD'))) as [END DATE] resident Data group by [Contract NO.],[Audit Result],Idtmp; drop table Data; drop fields Idtmp;

output:

Re: The internal logic values are in the script

wood — Sun, 22 Nov 2020 13:25:36 GMT

thank you Zarra