topic How can I re-reference a custom fields in the script? in QlikView

How can I re-reference a custom fields in the script?

Wed, 30 Mar 2011 11:10:05 GMT

I have two custom fields created in my script;

if( blah blah, 'True','False') as T1

if(blah blah, 'True','False') as T2

How would I create a third field such as:

If(T1 = 'True' and T2 = 'True, 'True', 'False') as T3

At the moment it's erroring as I don't think I can use a custom field in another custom field??

Thanks!!!! Mike

How can I re-reference a custom fields in the script?

Miguel_Angel_Baeyens — Wed, 30 Mar 2011 11:22:26 GMT

Hello Mike,

Since these values are in the same record, use the same conditionals for t3, like

If(If(blah blah, 'True', 'False') = 'True' AND If(blah blah, 'True', 'False') = 'True', 'True', 'False') AS T3

Hope that helps.

How can I re-reference a custom fields in the script?

Wed, 30 Mar 2011 11:29:24 GMT

Thanks for that... I considered that option but actually I have T1 - T7 and the if statements are complex so didn't really want to duplicate... would be tricky to edit down the line.

If no other option then that's what I'll do but wondering if there is anything tidier?

How can I re-reference a custom fields in the script?

Miguel_Angel_Baeyens — Wed, 30 Mar 2011 11:36:49 GMT

Hi,

You can do two loads, one with T1 and T2 (according to your example) and the second with T3. But anyway you may loop through all possible values and tables:

Step1:LOAD If(Condition) AS T1, If(Condition2) AS T2FROM Source; Step2:LOAD *, If(T1 = 'True' AND T2 = 'True', 'True', 'False') AS T3RESIDENT Step1; DROP TABLE Step1; // needed to avoid unwanted synthetic keys

Anyway, looping in may experience is always slower than creating those 7 fields in one load.

Hope that helps.

How can I re-reference a custom fields in the script?

vidyut — Wed, 30 Mar 2011 11:42:22 GMT

In the single load you cannot use the custom fields to define another new field.

To do that, you could use a preceding load, which would be the best in this case:

Load

If(T1='True' and T2='True','True','False') as T3,

*; //Notice how we don't specify the Tablename in the preceding load....

Load

if( blah blah, 'True','False') as T1,

if(blah blah, 'True','False') as T2

Resident BlahTable;