topic Re: Variable in Load script in QlikView

Variable in Load script

Wed, 28 May 2014 08:52:30 GMT

Hi,

I have a conditional in Load script as stated below.I want to replace it using some variable or other approach instead of hardcoding the value.

If(EX_NUMBER='RP_EX_15' or EX_NUMBER= 'RP_EX_54' or EX_NUMBER = 'RP_EX_22' OR EX_NUMBER= 'RP_EX_27' OR EX_NUMBER= 'RP_EX_80' or EX_NUMBER ='RP_EX_74' or EX_NUMBER ='RP_EX_76',PRIOR,CURRENT) as Territory,

I'm able to achieve this by -

SET vEXNUMBER=EX_NUMBER='RP_EX_15' or EX_NUMBER= 'RP_EX_54' or EX_NUMBER = 'RP_EX_22' OR EX_NUMBER= 'RP_EX_27' OR EX_NUMBER= 'RP_EX_80' or EX_NUMBER ='RP_EX_74' or EX_NUMBER ='RP_EX_76';

Then

Load

If('$(vEXNUMBER)',PRIOR,CURRENT) as Territory,

Is there any other convenient/Better performance way to achieve this??

Thanks.

Re: Variable in Load script

hic — Wed, 28 May 2014 09:03:47 GMT

I would use Exists().

If you first define all values you want to compare with:

ListOfValues: Load * Inline

[EX:NUMBER

RP_EX_15

RP_EX_54

... ];

then you can use this in your Load:

If( Exists(EX_NUMBER) ,PRIOR, CURRENT) as Territory,

HIC

Re: Variable in Load script

Wed, 28 May 2014 09:32:04 GMT

Thanks for the quick response Henric,

I'm using the same expression in multiple tables.I think Exist ll be fine.But as i'm already moving to Deployment, Bit confused about adding another table to the datamodel.

e.g- in One table it's like-

TBL:

Load RULE_ID,

SF&'_'& If(EX_NUMBER='RP_EX_15' or EX_NUMBER= 'RP_EX_54' or EX_NUMBER = 'RP_EX_22' OR EX_NUMBER= 'RP_EX_27' OR EX_NUMBER= 'RP_EX_80' or EX_NUMBER ='RP_EX_74' or EX_NUMBER ='RP_EX_76',PRIOR,CURRENT)&'_'&PRODUCT_ID&'_'&CITY as EX_PROD_ID,

COUNTRY,

ZIP

FROM....;

Thanks again.

Re: Variable in Load script

hic — Wed, 28 May 2014 09:43:26 GMT

If you add a

Drop Table ListOfValues ;

in the end of the script, you will not change the data model.

HIC

Re: Variable in Load script

jagan — Wed, 28 May 2014 09:46:21 GMT

Hi,

Try using Match function

TBL:

Load RULE_ID,

SF&'_'& If(Match(EX_NUMBER, 'RP_EX_15', 'RP_EX_54', 'RP_EX_22', 'RP_EX_27', 'RP_EX_80', 'RP_EX_74', 'RP_EX_76'),PRIOR,CURRENT)&'_'&PRODUCT_ID&'_'&CITY as EX_PROD_ID,

COUNTRY,

If(Match(EX_NUMBER, 'RP_EX_15', 'RP_EX_54', 'RP_EX_22', 'RP_EX_27','RP_EX_80', 'RP_EX_74', 'RP_EX_76'),PRIOR,CURRENT) as Territory,

ZIP

FROM....;

Regards,

Jagan.

Re: Variable in Load script

Wed, 28 May 2014 10:01:36 GMT

Yep That i did Henric.

I think it's better to go ahead with this instead of Variable.

I have the same EX_NUMBER field which is having all the EX_NUMBER values, But i'm taking a list of values for my expression.That's one think to note down too.I hope that should not be a worry case.

ListOfValues:

Load * Inline

[EX_NUMBER

RP_EX_15

RP_EX_54

... ];

then you can use this in your

Load:

If( Exists(EX_NUMBER) ,PRIOR, CURRENT) as Territory,

EX_NUMBER,

CITY,

COUNTRY

FROM...(qvd);

Re: Variable in Load script

Wed, 28 May 2014 10:03:08 GMT

Hi Jagan,

I had this expression before.

I'm looking for something like variable/Textfile using variable etc. instead of hardcoded values of EX_NUMBER

Thanks.