Generic Load

[#1]

This post is related to my previou post Set Condition in Load Script and I got Rob Wunderlich and  Gysbert Wassenaar's help.This post is more complicated.

So basically I have 3 tables as below,

Table_Initial:  

Case_ID	Initial_Date
10001	1/3/2007
10002	3/28/2010

Table_Followup:  

Case_ID	Followup_Date
10001	1/10/2007
10002	04/06/2010
10002	4/12/2010

Data_Routing:

There are three types of States, Data Entry (From_State '3' to To_State '4'), Review (From_State '4' to To_State '1001'), Finalize (From_State '1001' to To_State '1002').

Case_ID	From_State	To_State	Route_Date
10001	3	4	1/3/2007 08:30am
10001	1001	1002	1/8/2007 15:30pm
10001	1002	1003	1/8/2007 16:40pm
10001	3	4	1/11/2007 10:15am
10002	3	4	03/29/2010 9:45am
10002	4	3	3/29/2010 10:10am
10002	3	4	3/29/2010 14:15pm
10002	1001	1002	04/03/2010 10:15am
10002	1002	1003	04/04/2010 9:00am
10002	3	4	04/07/2010 12:15pm
10002	1002	1003	04/10/2010 9:00am
10002	3	4	04/13/2010 10:30am
10003	1002	1003
10003	3	4

My goal here is to get specific date for each of the state based on Initial or Followup Receive_Date. The challenge is how can I get different rows based on different Initial or Followup Receive_Date, as well as matching the Route_Date to specific states. Especially there are maybe more than 1 Followup Receive_Date for the same Case_ID.

Case_ID	Receive_Date	Data Entry Date	Review Date	Finalize Date
10001	1/3/2007	1/3/2007 08:30am	1/8/2007 15:30pm	1/8/2007 16:40pm
10001	1/10/2007	1/11/2007 10:15am
10002	3/28/2010	3/29/2010 14:15pm	04/03/2010 10:15am	04/04/2010 9:00am
10002	04/06/2010	04/07/2010 12:15pm	04/03/2010 10:15am	04/10/2010 9:00am
10002	4/12/2010	04/13/2010 10:30am

If any of you can share your thoughts or the possible ways to achieve my goal, please let me know.  Please find the attached example sample that may help you to understand my requirement easier.

Thanks in advance,

Becky

[#2]

Like this... Not sure why the Right Join is not working as well...Why are we using AutoNumber here?

[sunny_talwar]

AutoNumber seems to run fine for me. Can you check and run the attached

[#4]

I cannot. The app broke down when I try to run the script. Here I only posted a sample QVW file. But there are tons of records in the non-sample QVW file. I think it's the combination of using both AutoNumber() and RowNo(), just not sure why. It also acted like some type of virus. As my screen would blinking after I force quit the dead QlikView app. So I have to reboot my computer.