Apply final status to prior rows

[shaheermecci]

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

[ashishkalia · Accepted solution]

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!!

AK

*Pl mark correct if works.*