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' asDATA_ENTRY, 'NA' as Review_DATE, 'NA' as Finalize_DATE
STORE * FROM Table A INTO Table A;
In the Data Model layer, I set up IF condition, but this wans't work
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.
There are hundred and thousands of data. So I am not sure how to use Inline Load here as I cannot type they all in. My bad didn't express it clearly. So instead of using Inline Load, can I use regular load? How do you think if I modify the script as below? And why at the end of the script we only drop table Data34?
Inline LOAD was used only for demonstration here in the post. You can load from QVD, xls, or any other source, No need to do an additional Resident. You would still use the same Generic Load statement:
'Date' & From_State & To_State,
Each pair of From_State & To_State routing is going to generate a separate table named Tablename.Date&FromState&To_State For example, in the load above the 1001 & 1002 will generate a table "CaseRouting.Date1001002".
I dropped the "43" table because you did not seem interested in the From_State=3 to To_State=4 events.
2. As you explained, I understand that each pair of "Date & To_State" routing is going to generate a separate table. And we can drop any tables afterward. Instead of drop a bunch of tables/events that I am not interested in, is there a way not to load they before generate those table? For example, I only want to show To_State, From_State with values (3,4,1001,1002). However, there are more than (3,4,1001,1002) values exsting in the To_State, From_State data fileds. I tried to do this, but it didn't work.