Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

Set Analysis / Like Operator Help!

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:

if

(

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

(

Hope you can help me!! Many thanks!
Dayna

1 Solution

Accepted Solutions
deepakk
Partner - Specialist III
Partner - Specialist III

hi Dayna ,

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

Create field at script level like

if

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



Expression :

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





View solution in original post

8 Replies
deepakk
Partner - Specialist III
Partner - Specialist III

hi,

try this code,

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



Dayna
Creator II
Creator II
Author

Hello Deepak,

Unfortunately it still brings back zero!

Kind Regards,
Dayna

deepakk
Partner - Specialist III
Partner - Specialist III

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)





Dayna
Creator II
Creator II
Author

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

deepakk
Partner - Specialist III
Partner - Specialist III

hi Dayna,

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

if

(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.



Dayna
Creator II
Creator II
Author

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

deepakk
Partner - Specialist III
Partner - Specialist III

hi Dayna ,

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

Create field at script level like

if

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



Expression :

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





Dayna
Creator II
Creator II
Author

Deepak, your a star!! Thank you!!