if condition

[raadwiptec]

hi all,

this is my scenario.

if product name contains 'decoder'  then 'DC'

if product name contains 'connectorsr'  then 'CN'

if product name contains 'capacitorr'  then 'CP'

so it should look like below

id ¦ product  ¦ category

1     decoder  DC

2     connector  CN

3   capacitor     CP

please suggest both on script and expression

[raadwiptec]

Load,

Prodid,

Pick(WildMatch(product, '*decoder*', '*connector*', '*capacitor*', product), 'DC', 'CN', 'CP', 'Not Mapped') as category

from

product.txt .....

so iam getting the output like this

id ¦ Product ¦ Category

1  decoder  DC

1  decoder  CN

1  decoder  CP

1  decoder  Not Mapped

2  connector DC

2  connector CN

2  connector CP

2  connector Not Mapped

[sunny_talwar]

Are you doing a join to another table? I don't see Product column in your load from product.txt?

[Anonymous]

Rather than doing at the front end try this at back end (script level). This is fasten your object calculation;

if(wildmatch(product, '*decoder*'),'DC' ,

     if(wildmatch(product , '*connector*'),'CN' ,

          if(wildmatch(product, '*capacitor*'),'CP')) As category

Hope this helps.

Imran K

[MarcoWedel]

Hi,

another solution could be:

tabProducts:
LOAD RecNo() as ID, *
Inline [
Product
decoder
decoderNew
someotherdecoder
somedecoder and Connector
one connector and another connector
resistor
inductor
any connector
differentconnector
connectorOld
capacitor
capital CAPACITOR
veryoldcapacitor
CapacitorPretendingToBeAConnector
somethingCompletelyDifferent
];

mapProdCat:
Mapping LOAD Product, '@start@'&Category&'@end@'
INLINE [
    Product, Category
    decoder, DC
    connector, CN
    capacitor, CP
];

tabCategory:
LOAD Distinct
    ID,
    TextBetween(MapSubString('mapProdCat', Lower(Product)),'@start@','@end@',IterNo()) as Category
Resident tabProducts 
While IterNo()<=SubStringCount(MapSubString('mapProdCat', Lower(Product)),'@start@');

LOAD ID,
    'not mapped' as Category
Resident tabProducts
Where IsNull(Lookup('ID','ID',ID,'tabCategory'));

hope this helps

regards

Marco

[raadwiptec]

hi sunny,

no it is product...no other join from other tables...

[MarcoWedel]

or slightly shorter:

tabProducts:
LOAD RecNo() as ID, *
Inline [
Product
decoder
decoderNew
someotherdecoder
somedecoder and Connector
one connector and another connector
resistor
inductor
any connector
differentconnector
connectorOld
capacitor
capital CAPACITOR
veryoldcapacitor
CapacitorPretendingToBeAConnector
somethingCompletelyDifferent
];

mapProdCat:
Mapping LOAD Product, '@start@'&Category&'@end@'
INLINE [
    Product, Category
    decoder, DC
    connector, CN
    capacitor, CP
];

tabCategory:
LOAD Distinct
    ID,
    If(SubStringCount(MapSubString('mapProdCat', Lower(Product)),'@start@'),TextBetween(MapSubString('mapProdCat', Lower(Product)),'@start@','@end@',IterNo()),'not mapped') as Category
Resident tabProducts
While IterNo()<=RangeMax(SubStringCount(MapSubString('mapProdCat', Lower(Product)),'@start@'),1);

hope this helps

regards

Marco

[raadwiptec]

hi marco,

I do not understand how to use your code. what is "start, "end... etc..

can you please show me the script for the columns I requested.

[MarcoWedel]

Hi,

the resulting data model contains only your specified columns: ID, Product, Category.

'start' and 'end' are just string delimiters to identify the category values inserted into the product string by the MapSubString()-function.

Post some example application if you need help to integrate this solution.

regards

Marco

[raadwiptec]

rhanks. I used a

match(product,'Capacitor','DC')  it worked good.

[mahendrankr]

This was super helpful