Set Condition in Load Script

[#1]

Hello,

I have a table includes data fields: Case_ID, From_State, To_State, Route_Date. 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').

My goal here is to get specific date for each of the state. Please find the attached example excel sheet that may understand my requirement easier.

And this is what I did as below,

In Transformation layer, I created 3 new columns based on exsiting data fields: Data_Entry, Review_Date and Finalize_Date.

Table A:
LOAD
CASE_ID ,
ROUTE_DATE,
From_State,
To_State,
'NA' as DATA_ENTRY,
'NA' as Review_DATE,
'NA' as Finalize_DATE

From...table A;

STORE * FROM Table A INTO Table A;

In the Data Model layer, I set up IF condition, but this wans't work

Table A:

Load

CASE_ID ,
ROUTE_DATE,
From_State,
To_State,

If(FR_STATE_ID = '3' AND TO_STATE_ID = '4', ROUTE_DATE, 'NA') as DATA_ENTRY_DATE,
If(FR_STATE_ID = '4' AND TO_STATE_ID = '100010', ROUTE_DATE, 'NA') as QC_DATE,
If(FR_STATE_ID = '100010' AND TO_STATE_ID = '100011', ROUTE_DATE, 'NA') as MR_DATE

From Table A;

What I got is only the Finalize_Date have date records in it. But the data hasn't connect to Case_ID and other data fields. Data_Entry and Review_Date only have 'NA' in it. Please let me know any of your suggestions, how to make this right.

In the Presentation layer: which related to the Final Result I would like to get in the attached excel sheet. But I haven't gone for that far yet.

It's a little bit complex, please let me know if you are having trouble to understand my question or the goal.

Thanks in advance,

Becky

[#2]

Here is the sample. Please take a look at it. Thanks Rob.

[#3]

I guess I know where went wrong. I am not sure why and what's the theroy behind.

In the attached case, it load 4 data fields. If I commented "From_State_ID" or "Route Date", then there is no synthetic key. But how if I want to show "From_Sate_ID" and "Route Date" in the chart?  And I cannot only load 2 data fields as it shows Generic load needs at least three Fields.

[#4]

Hi Rob,

I have another question. I set up the value of different states in different variables. Such as below, 

SET DataEntryDate = '3,30';
SET ReviewDate = '1001';
SET FinalizationDate = '1002';

Match (TO_STATE_ID, $(DataEntryDate)) OR
Match (TO_STATE_ID, $(ReviewDate)) OR
Match (TO_STATE_ID, $(FinalizationDate)) ;

Can I use variables while renaming the generated data fields? What would be the correct sytax to replace "Date3", "Date1001", "Date 1002" as below?

RENAME FIELD Date3 TO Data_Entry_Date;
RENAME FIELD Date1001 TO Review_Date;
RENAME FIELD Date1002 TO Finalization_Date; 

Thank you!

Becky

[#5]

Here is a update.

I figured it out by using the way in the attached QVW. I also tried the technique you shared to consolidate new generated tables.However, I am still in process of figuring out how to use variables in RENAME statement.