Apply final status to prior rows

shaheermecci — Sat, 16 Nov 2024 00:59:36 GMT

Hello,

I have high transaction data (15-20 million rows) which contains status per transaction. Transactions can fail, but may eventually "Pass".

The final status is important. Failed transactions need to get tagged with Pass so they don't need triaging.

# of fails can be arbitrary so nested Previous function only goes that far. I am not sure about looping. May take too long to process large volume.

I am looking for an output with the last 2 derived fields with Final Status and Resolved Dates based on the pass status for each Script# + Medication combo.

I am trying to get to this output. Transactions attempted = 15, Scripts impacted = 2, Meds impacted = 3, Scripts resolved = 1

Any help is appreciated. Thanks!

Id	Tran Date	Script#	Medication	Status	Final Status	Resolved Date
1	3/10/2020 12:30	A	X	Fail	Pass	3/10/2020 12:32
2	3/10/2020 12:31	A	X	Fail	Pass	3/10/2020 12:32
3	3/10/2020 12:32	A	X	Pass	Pass	3/10/2020 12:32
4	3/10/2020 12:33	A	Y	Fail	Pass	3/10/2020 12:37
5	3/10/2020 12:34	A	Y	Fail	Pass	3/10/2020 12:37
6	3/10/2020 12:35	A	Y	Fail	Pass	3/10/2020 12:37
7	3/10/2020 12:36	A	Y	Fail	Pass	3/10/2020 12:37
8	3/10/2020 12:37	A	Y	Pass	Pass	3/10/2020 12:37
9	3/10/2020 12:38	B	P	Fail
10	3/10/2020 12:39	B	P	Fail
11	3/10/2020 12:40	B	P	Fail
12	3/10/2020 12:41	B	P	Fail
13	3/10/2020 12:42	B	P	Fail
14	3/10/2020 12:43	B	P	Fail
15	3/10/2020 12:44	B	P	Fail

Re: Apply final status to prior rows

ashishkalia — Tue, 24 Mar 2020 02:29:51 GMT

Hi @shaheermecci ,

You need to create one view where you need to identify the max date for all the Script+Medication pair and left join it to the main table based on your Script+Medication key.

I managed to get the desired output in the attached QVW.

Pl see and let me know if that works, in my opinion it is the only possible way to that.

Cheers!!

*Pl mark correct if works.*

topic Re: Apply final status to prior rows in QlikView

Apply final status to prior rows

Re: Apply final status to prior rows