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

[Gysbert_Wassenaar]

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.

[rwunderlich · Accepted solution]

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

http://masterssummit.com

http://qlikviewcookbook.com

[#4]

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?

[#5]

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;

[rwunderlich]

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

http://masterssummit.com

http://qlikviewcookbook.com

[#7]

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

[rwunderlich]

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

http://masterssummit.com

http://qlikviewcookbook.com

[#9]

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

[rwunderlich]

Can you post a sample QVW?

-Rob