Variable in Load script

[#1]

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.

[hic]

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

[#3]

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,

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,

ZIP

FROM....;

Thanks again.

[hic]

If you add a

     Drop Table ListOfValues ;

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

HIC

[jagan]

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.

[#6]

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

[#7]

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.