topic Re: Set Condition in Load Script in QlikView

Set Condition in Load Script

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Condition in Load Script

Gysbert_Wassenaar — Tue, 05 Jul 2016 15:56:53 GMT

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.

Re: Set Condition in Load Script

rwunderlich — Tue, 05 Jul 2016 16:08:41 GMT

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

Re: Set Condition in Load Script

Tue, 05 Jul 2016 17:02:41 GMT

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?

Re: Set Condition in Load Script

Tue, 05 Jul 2016 17:41:29 GMT

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;

Re: Set Condition in Load Script

rwunderlich — Tue, 05 Jul 2016 21:40:34 GMT

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

Re: Set Condition in Load Script

Wed, 06 Jul 2016 14:31:31 GMT

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 ];

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

Re: Set Condition in Load Script

rwunderlich — Wed, 06 Jul 2016 17:12:59 GMT

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

Re: Set Condition in Load Script

Thu, 07 Jul 2016 15:43:25 GMT

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

Re: Set Condition in Load Script

rwunderlich — Thu, 07 Jul 2016 15:50:22 GMT

Can you post a sample QVW?

-Rob

Re: Set Condition in Load Script

Thu, 07 Jul 2016 16:58:21 GMT

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

Re: Set Condition in Load Script

Thu, 07 Jul 2016 17:13:47 GMT

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.

Re: Set Condition in Load Script

Thu, 07 Jul 2016 17:28:40 GMT

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

Re: Set Condition in Load Script

Thu, 07 Jul 2016 18:44:18 GMT

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.