Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

if/wildmatch with few condition

Hi Everyone

I'm a new in Qlikview and I want to learn more about it (I use Personal Edition at the moment). This is an example from my script:

table:

LOAD 'xxx'as producent, 

  if(WildMatch([Product Description],'*Dual*')=1,'2 Core','')

  or if(WildMatch([Product Description],'*Quad*')=1,'4 Core','')

  or if(WildMatch([Product Description],'*6C*')=1,'6 Core','')

  or if(WildMatch([Product Description],'*8C*')=1,'8 Core','')

  or if(WildMatch([Product Description],'*12C*')=1,'12 Core','')

  or if(WildMatch([Product Description],'*3,5"*')=1,'3,5"','')

  or if(WildMatch([Product Description],'*LFF*')=1,'3,5"','')

  or if(WildMatch([Product Description],'*2,5"*')=1,'2,5"','')

  or if(WildMatch([Product Description],'*SFF*')=1,'2,5"','')

  or if(WildMatch([Product Description],'*1,8"*')=1,'1,8"','')

  or if(WildMatch([Product Description],'*RDIMM*')=1,'RDIMM','')

  or if(WildMatch([Product Description],'*UDIMM*')=1,'UDIMM','')

  as [category2],

  if(WildMatch([Product Description],'*SCSI*')=1,'SCSI','')

  or if(WildMatch([Product Description],'*SAS*')=1,'SAS','')

  or if(WildMatch([Product Description],'*SATA*')=1,'SATA','')

  or if(WildMatch([Product Description],'*SSD*')=1,'SSD','')

  or if(WildMatch([Product Description],'*AMD*')=1,'AMD Opteron','')

  or if(WildMatch([Product Description],'*Intel*')=1,'Intel','')

  as [category3],

  // '' as compability,

  [US PN],

     PN,

     [Product Description],

     'Europe' as Region,

     if(WildMatch([another PN],'*no*')=1,Null(),[another PN])as [another PN],

     comments

FROM

[xxx.xls]

(biff, embedded labels, header is 1 lines, table is [Options$], filters(

Top(1, 'US PN'),

Top(2, 'PN'),

Top(3, 'Product Description'),

Top(9, 'another PN'),

Top(10, 'comments'),

Remove(Row, RowCnd(CellValue, 4, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

Of course this script is not optimal (many if conditions) and it don't work. My question is:

1) how to solve this problem (above)? Why operators like or, and don't working properly?

2) how to use if function with few conditions or a few different functions on one column (label)?

for example: if in [product description] is '*dell*'  and in [product description] is '*memory*' or '*DIMM*' then fill 'Dell Memory' if else ... as Category1;

3) how to use if function with few conditions or a few different functions on more than one column (label)?

for example: if in [product description] is '*dell*'  and in [coments] is '*processor*' or '*CPU*' then fill 'Processors dedicated to Dell' if else ... as Category1;

4)Where should I do this steps (I wolud like to do it into table but maybe it isn't correct)? I have the same problem with another  functions: replace, wildmatch etc.

I have many dirty tables to change (to do in one standard) and I can't change source files.

And another problem:

In one column called [Product Description] I have products and compability similar to  http://community.qlik.com/thread/113416

I've solved my problem with Replace(15, bottom, StrCnd(null)) or Replace(15, top, StrCnd(null)) but now I must to use both Replace function in one table and on one column.

5)  So, is there possible to use fill fuction with index (fill bottom from 1 to 15 and fill top from 15 to N ...) or another column condition (fill bottom untill in [desc.] ="end of section", then fill top from untill in [desc.] ="end of section"+1 to end) ?

6) Or maybe is possible to dinamically split this table in file wizard, do fill top on one part and fill below on another and join them ?

Thanks in advance for your reply or any helpful information.

Best regards

Ewelina

1 Solution

Accepted Solutions
Partner
Partner

Re: if/wildmatch with few condition

1) how to solve this problem (above)? Why operators like or, and don't working properly?

Hi Ewelina,

i think you should write the if Statements like this:

if(WildMatch([Product Description],'*Dual*')=1,'2 Core',

     if(WildMatch([Product Description], '*Quad*)=1,'4 Core',

          if(WildMatch......

                    ......) as [Category2],

....

in your case the or- lines are never reached because you set " as Category2 if it's not 2 Core.

View solution in original post

3 Replies
Partner
Partner

Re: if/wildmatch with few condition

1) how to solve this problem (above)? Why operators like or, and don't working properly?

Hi Ewelina,

i think you should write the if Statements like this:

if(WildMatch([Product Description],'*Dual*')=1,'2 Core',

     if(WildMatch([Product Description], '*Quad*)=1,'4 Core',

          if(WildMatch......

                    ......) as [Category2],

....

in your case the or- lines are never reached because you set " as Category2 if it's not 2 Core.

View solution in original post

Re: if/wildmatch with few condition

Hi,

If you want to use nested if else then use solution given by Lukas Puschner

but if you want match two or more condition in same if then try like this,

2>

how to use if function with few conditions or a few different functions on one column (label)?

for example: if in [product description] is '*dell*'  and in [product description] is '*memory*' or '*DIMM*' then fill 'Dell Memory' if else ... as Category1;

if(wildmatch([product description] ,'*dell*') and wildmatch([product description] ,'*memory*','*DIMM*'),'Dell Memory') as Category1.

3>

how to use if function with few conditions or a few different functions on more than one column (label)?

for example: if in [product description] is '*dell*'  and in [coments] is '*processor*' or '*CPU*' then fill 'Processors dedicated to Dell' if else ... as Category1;

if(wildmatch([product description] ,'*dell*') and wildmatch([coments] ,'*processor*','*CPU*'),'Processors dedicated to Dell') as Category1.

4>

You have to this in script and this is only way.

Regards,

PS

Great dreamer's dreams never fulfilled, they are always transcended.
MVP
MVP

Re: if/wildmatch with few condition

better if you can post some data

I try to explain how you can use match (and similar) to reduce if with an example

Hope it help syou

Source:

load rowno() as id,

product;

load * inline [

product

dual

quad

rdimm

udimm

scsi

sas

sata

amd

intel

];

Table:

NoConcatenate

load

id,

product,

pick(wildmatch(product, '*dual*', '*quad*', '*rdimm*', '*udimm*', '*'), '2 core', '4 core', 'rdimm', 'udimm', 'UNKNOWN') as category2,

pick(wildmatch(product, '*scsi*', '*sas*', '*sata*', '*amd*', '*intel*', '*'), 'SCSI', 'SAS', 'SATA', 'AMD','INTEL','UNKNOWN') as category3

resident Source;

drop table Source;

1.png