Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
If you add a
Drop Table ListOfValues ;
in the end of the script, you will not change the data model.
HIC
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.
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);
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.