Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Here's a flexible approach using GENERIC LOAD:
Data:
GENERIC
LOAD
Case_ID,
'Date' & From_State & To_State,
Route_Date
INLINE [
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
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
10003 1002 1003 .
10003 3 4 .
] (delimiter is '\t')
;
RENAME FIELD Date34 TO [Data Entry Date];
RENAME FIELD Date10011002 TO [Review Date];
RENAME FIELD Date10021003 TO [Finalize Date];
// Optional - We don't care about the 4-3 transition
DROP TABLE Data.Date43;
Example attached, see also Use cases for Generic Load | Qlikview Cookbook
-Rob
Try this:
TableA:
LOAD
Case_ID
From_State
To_State
Route_Date
If(From_State=3 and From_State=1, Route_Date) as Data_Entry_Date,
If(From_State=1001 and From_State=1002, Route_Date) as Review_Date,
If(From_State=1002 and From_State=1003, Route_Date) as Finalize_Date
FROM
...source_table...
;
Result:
LOAD
Case_ID,
max(Data_Entry_Date) as Data_Entry_Date,
max(Review_Date) as Review_Date,
max(Finalize_Date) as Finalize_Date
RESIDENT
TableA
GROUP BY
Case_ID
;
Note, the dates need to be real dates since the max() function won't work on text values. Use the Date#() if necessary to convert text values into date values.
Here's a flexible approach using GENERIC LOAD:
Data:
GENERIC
LOAD
Case_ID,
'Date' & From_State & To_State,
Route_Date
INLINE [
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
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
10003 1002 1003 .
10003 3 4 .
] (delimiter is '\t')
;
RENAME FIELD Date34 TO [Data Entry Date];
RENAME FIELD Date10011002 TO [Review Date];
RENAME FIELD Date10021003 TO [Finalize Date];
// Optional - We don't care about the 4-3 transition
DROP TABLE Data.Date43;
Example attached, see also Use cases for Generic Load | Qlikview Cookbook
-Rob
Hi Gysbert,
Thanks a lot for your input.
But I got similar result as I did earlier. The Case ID is not associate with any of the three stages...
"Route_Date" is timestamp format. I will need to keep the format. Not sure if this effect the result as you said need to use Date#(). How do you think?
Thanks a lot Bob.
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?
LOAD
Case_ID,
From_State,
To_State
FROM
[CASE_ROUTING.QVD](qvd);
GENERIC
LOAD
Case_ID
'Date' & From_State & To_State,
ROUTE_DATE
RESIDENT CASE_ROUTING;
RENAME FIELD Date34 TO [Data Entry Date];
RENAME FIELD Date41001 TO [Review Date];
RENAME FIELD Date10011002 TO [Finalize Date];
DROP TABLE CASE_ROUTING.Date34;
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:
CaseRouting:
GENERIC
LOAD
Case_ID,
'Date' & From_State & To_State,
Route_Date
FROM [CASE_ROUTING.QVD](qvd);
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.
-Rob
Thanks for sharing more details which really helped me think through. But I am more confused here as well. A couple of questions in my mind:
1. If we can use the Generic Load. Shall I still use the Regular Load above the Generic Load. If so, why do we need two Load statement?
CASE_ROUTING:
LOAD
Case_ID,
ROUTE_DATE,
FR_STATE,
TO_STATE
FROM
[CASE_ROUTING.QVD]
(qvd);
GENERIC
LOAD
Case_ID,
'Date' & TO_STATE,
ROUTE_DATE
FROM
[CASE_ROUTING.QVD]
(qvd);
RENAME FIELD Date3 TO Data_Entry_Date;
RENAME FIELD Date1001 TO Review_Date;
RENAME FIELD Date1002 TO Finalization_Date;
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.
Dummy_A:
LOAD *
INLINE [
FR_STATE_ID
3
4
1001
1002
1003 ];
Dummy_B:
LOAD *
INLINE [
TO_STATE_ID
3
4
1001
1002
1003 ];
CASE_ROUTING:
LOAD
Case_ID,
ROUTE_DATE,
FR_STATE,
TO_STATE
FROM
[CASE_ROUTING.QVD]
(qvd);
GENERIC
LOAD
Case_ID,
'Date' & TO_STATE,
ROUTE_DATE
FROM
[CASE_ROUTING.QVD]
(qvd);
RENAME FIELD Date3 TO Data_Entry_Date;
RENAME FIELD Date1001 TO Review_Date;
RENAME FIELD Date1002 TO Finalization_Date;
Drop Table Dummy_A;
Drop Table Dummy_B;
I also tried drop those tables: Drop Table CASE_ROUTING.Date21; But when I runthe script, it show me error "Table not found - DROP TABLES statement". How can I only shows the specific values?
Thanks a lot,
Becky
You don't need two load statements. One will do. And you can filter the input with a WHERE clause to include only the transitions you are interested in.
Data:
GENERIC
LOAD
Case_ID,
'Date' & From_State & To_State,
Route_Date
FROM
Example.xlsx
(ooxml, embedded labels, table is Sheet1)
WHERE
From_State&To_State = 34
OR From_State&To_State = 10011002
OR From_State&To_State = 10021003
;
RENAME FIELD Date34 TO [Data Entry Date];
RENAME FIELD Date10011002 TO [Review Date];
RENAME FIELD Date10021003 TO [Finalize Date];
-Rob
Hi Bob,
I tried to use only one Load statement which is Generic Load. However, it created synthetic key with CaseID + 'Date' & To_State + Route date. This won't happen if I use two load statement.
Could you kindly let me know how to eliminate synthetic key in one load?
Thanks,
Becky
Can you post a sample QVW?
-Rob