Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
raadwiptec
Creator II
Creator II

if condition

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

19 Replies
raadwiptec
Creator II
Creator II
Author

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

decoder  DC

decoder  CN

decoder  CP

decoder  Not Mapped

connector DC

connector CN

connector CP

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
Not applicable

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:

QlikCommunity_Thread_188907_Pic1.JPG

QlikCommunity_Thread_188907_Pic2.JPG

QlikCommunity_Thread_188907_Pic3.JPG

QlikCommunity_Thread_188907_Pic4.JPG

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

rhanks. I used a

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

mahendrankr
Contributor III
Contributor III

This was super helpful