if/wildmatch with few condition

[#1]

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

[lukaspuschner · Accepted solution]

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.

[PrashantSangle]

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

[maxgro]

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;