topic Set Analysis / Like Operator Help! in QlikView

Set Analysis / Like Operator Help!

Dayna — Fri, 25 Feb 2011 10:43:23 GMT

Hello All,

I'm wondering if you could advise as to whether the following is possible... I want combine an if statement with a LIKE operator into my Set Analysis.

What I am trying to achieve is, for the gltr_acc = '06200', then sum(amt) for any parts that have the pt_part_type like 'R%'...

So basically, for other account codes, I want to sum everything, but when the account code is 06200, then I only want to show the products where the pt_part_type beings with R.

Now, my overall total must include the values from the 06200 account code with the above, and also the total from the other account codes for everything.. I tried something like this, but it doesn't bring back any results:

(

(gltr_acc='06200' ,sum({$<pt_part_type = 'R%'>} gltr_amt)

(

Hope you can help me!! Many thanks!
Dayna

Set Analysis / Like Operator Help!

deepakk — Fri, 25 Feb 2011 10:49:32 GMT

hi,

try this code,

if(gltr_acc='06200' ,sum({$<pt_part_type = 'R*'>} gltr_amt))

Set Analysis / Like Operator Help!

Dayna — Fri, 25 Feb 2011 10:55:58 GMT

Hello Deepak,

Unfortunately it still brings back zero!

Kind Regards,
Dayna

Set Analysis / Like Operator Help!

deepakk — Fri, 25 Feb 2011 11:05:15 GMT

hi Dayana,

Where are you using this expression, pivot table or textobject

if you are using it in pivot table use it in below way

if(Account='1221',sum({$<Type = {"R*"}>} Value

))

instead of single quotes and % it should be double quotes and * (I made a mistake of puttting single quotes in the above case)

Set Analysis / Like Operator Help!

Dayna — Fri, 25 Feb 2011 11:15:42 GMT

Hello Deepak,

It was a straight table so that did the job! Thank you!

For my next question, this works perfectly for when the only account is 06200, but now I need a sum of all the other accounts (with no filter) plus this value.. Obviously the IF statement will only sum value if the account is 06200.

So my data would look like this:

Product Value
Prod1 10,0000

(The value would sum all the account codes, but only take the value for account 06200 where the type begins with R)

Many thanks for your help!

Kind Regards,
Dayna

Set Analysis / Like Operator Help!

deepakk — Fri, 25 Feb 2011 11:29:18 GMT

hi Dayna,

I think doing this at script level will make life easier.

(Account<>'06200','N',if(wildmatch(Type,'R*')>0,'Y',null())) as SubType

In the above code we are putting all codes other than 06200 as N and for code 06200 with R* as Y and rest as null

Now in Front end we can write Sum( {<SubType ={"Y","N"}>} Values)

I hope this helps.

Set Analysis / Like Operator Help!

Dayna — Fri, 25 Feb 2011 11:31:54 GMT

Hello Deepak,

I agree with you, unfortunately my data can't be joined and the three fields are in 3 different tables! Sod's law.

I got around the above with this:

if(gltr_acc='06200',sum({$<pt_part_type = {"R*"}>} gltr_amt),if(gltr_acc <> '06200',sum(gltr_amt)))

However, I've noticed that if I take gltr_acc out as a dimension it doesn't work.. Any ideas?

Kind Regards,
Dayna

Set Analysis / Like Operator Help!

deepakk — Fri, 25 Feb 2011 12:33:01 GMT

hi Dayna ,

I got a solution for this . I hope this helps you out.

Create field at script level like

(Account<>'1221','N','Y') as Rem

Expression :

sum({<Account={1221},Type={"R*"}> + <Rem={"N"}>} Value)

Set Analysis / Like Operator Help!

Dayna — Fri, 25 Feb 2011 13:22:46 GMT

Deepak, your a star!! Thank you!!