topic Re: if condition in QlikView

if condition

raadwiptec — Fri, 23 Oct 2015 13:16:31 GMT

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

Re: if condition

sunny_talwar — Fri, 23 Oct 2015 13:19:08 GMT

May be this:

for script -

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

for expression -

=Pick(WildMatch(product, '*decoder*', '*connector*', '*capacitor*'), 'DC', 'CN', 'CP')

Re: if condition

lironbaram — Fri, 23 Oct 2015 13:21:35 GMT

script

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

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

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

calculated Dimension

aggr(,

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

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

if(wildmatch(product , '*cpacitor*'),'CP')),product)

Re: if condition

raadwiptec — Fri, 23 Oct 2015 13:30:47 GMT

hi sunny,

if this 3 does not match then the condition should be 'not mapped'. how to get that?

Re: if condition

sunny_talwar — Fri, 23 Oct 2015 13:36:33 GMT

Try this:

for script:

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

for expression:

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

Re: if condition

HirisH_V7 — Fri, 23 Oct 2015 13:53:16 GMT

Hi ,

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

You can use like this in script .

Thanks,

hirish

Re: if condition

raadwiptec — Fri, 23 Oct 2015 13:53:20 GMT

thanks sunny just one more question as my data looks little messy..how can I have 2 conditions

for ex:

if product contains decoder and decoderNew then 'DC'

Re: if condition

sunny_talwar — Fri, 23 Oct 2015 14:05:30 GMT

the asterisk next decoder will take care of decoderNew

Re: if condition

raadwiptec — Fri, 23 Oct 2015 14:26:28 GMT

Hi sunny,

Iam also getting strange results means. I could see all the codes gets repeated thrice in each

Re: if condition

sunny_talwar — Fri, 23 Oct 2015 14:27:31 GMT

Can you share your script?

Re: if condition

raadwiptec — Fri, 23 Oct 2015 14:43:27 GMT

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

Re: if condition

sunny_talwar — Fri, 23 Oct 2015 14:47:05 GMT

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

Re: if condition

Anonymous — Fri, 23 Oct 2015 14:57:28 GMT

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

Re: if condition

MarcoWedel — Fri, 23 Oct 2015 23:11:07 GMT

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

Re: if condition

raadwiptec — Sun, 25 Oct 2015 18:12:27 GMT

hi sunny,

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

Re: if condition

MarcoWedel — Sun, 25 Oct 2015 22:11:01 GMT

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

Re: if condition

raadwiptec — Mon, 26 Oct 2015 07:25:04 GMT

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.

Re: if condition

MarcoWedel — Wed, 28 Oct 2015 23:55:52 GMT

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

Re: if condition

raadwiptec — Thu, 29 Oct 2015 08:50:23 GMT

rhanks. I used a

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

Re: if condition

mahendrankr — Tue, 23 Feb 2021 13:27:01 GMT

This was super helpful