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

[sunny_talwar]

Partially working, will continue to look

[sunny_talwar · Accepted solution]

Slight improvement here (but only slight)

[vinieme12]

Hi,

See attached it it helps, a few questions though!

In your data there are 4 events for Case_ID :10002 entering DataEntryStage, but in your o/p there are only three rows for 10002,

The one in red is missing from your o/p table(see below), any reasons why?

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

Also there is no Fromstate_Tostate (1001-1002)  record of the second dataentry state for CaseID:10002 in the data table;

How did we assume the ReviewDate to be the same as previous ReviewDate?

Is it that only when a CaseID is finalized we can assume review date to be same as previous RecordDate else null() ??

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

Cheers

V

[sunny_talwar]

No, that won't help. I need an incremental counting for each combination of Case_ID&From_State&To_State. The difference between the two expressions, can be seen below:

Case_ID	From_State	To_State	AutoNumber(Case_ID&From_State&To_State)	AutoNumber(RowNo(), Case_ID&From_State&To_State)
10002	3	4	1	1
10002	3	4	1	2
10002	3	4	1	3
10002	3	4	1	4

[#6]

Hi Vineeth,

Thanks a lot for spending your time and show the inputs. The attachment is the first step I was looking for. I am looking forward something more here, like Sunny T's solution.

Back to your questions, great questions which is why makes this metric more complicated.

So the Initial_Date for 10002 was 01/28/2010. Then the following activities happened. The tricky thing here is it could from State 3 to State 4, as well as from State 4 back to State 3. So you can see  from 3 - 4, then 4-3, then 3-4 again.

Case_ID

From_State

To_State

Route_Date

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

Then there are two Follow_Date 04/06/2010 and 04/12/2010, which make the process happened again and again.    

1st Follow_up:

10002	3	4	04/07/2010 12:15pm
10002	1002	1003	04/10/2010 9:00am

2nd Follow_up:            

10002

3

4

04/13/2010 10:30am

You are correct and I was adding wrong record into the final result table. The review_date 04/03/2010 shoudn't be there.

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/10/2010 9:00am
10002	4/12/2010	04/13/2010 10:30am

Please let me know if you have other ideas.

Thanks,

Becky

[vinieme12]

check the attached

[#8]

Hi Sunny... when I have the "RowNo()" with in AutoNumber fuction, I cannot run the script. I can ran the script after I removed the "RowNo()". But then I cannot get the correct result. I assuming one of the reasons is probably becuase there are lots of data. For the other reasons, I wasn't sure why.

Can you share with me that what's the reason we are using AutoNumber here? To try to avoid the synthetic key or more than that?

Thanks,

Becky

[sunny_talwar]

Can you share the script you are using today?

[#10]

Hi Vineeth

Thanks a lot for your time editing the script. Your script is more advanced than I knew...

One thing is the case could transfer from one state to another state. It is not nesserary a case process from Data_Entry to Review. It could be from Finalization to Review. So we don't want to to set the SateKey, 3-4, 1001 - 1002, 1002 - 1003 if I understand you correctly.