topic Re: If condition in set analysis in QlikView

If condition in set analysis

arvind_patil — Wed, 02 Aug 2017 06:34:08 GMT

Dear Experts,

I have one requirement.

ID Plant Value

100 A 100

200 A 200

100 B 250

400 A 300

400 B 280

400 C 220

I required sum of value based on this condition

1. If ID Start with 1 exclude Plant A.

2. If ID start with other than 1 then Sum Stock

How could i achieve this in single expression in without any selection.

I Have done some code Please help:

=sum({<ID={'1*'},Plant-={"A"}>+<Plant={"*"}>}Stock)

Thanks ,

Arvind Patil

Re: If condition in set analysis

antoniotiman — Wed, 02 Aug 2017 06:48:26 GMT

Hi Arvind,

maybe this

Sum({<ID={"=Left(ID,1) <> '1'"}>} Value)
or
Sum(If(Left(ID,1) <> 1,Value))

Regards,

Antonio

Re: If condition in set analysis

arvind_patil — Wed, 02 Aug 2017 06:52:35 GMT

Thanks for reply , but it will not work in my case

Thanks,

Arvind Patil

Re: If condition in set analysis

Kushal_Chawda — Wed, 02 Aug 2017 06:54:56 GMT

try this

sum({<ID={'1*'},Plant-={"A"}>+<ID-={'1*'},Plant={'*'}>}Stock)

Re: If condition in set analysis

antoniotiman — Wed, 02 Aug 2017 07:02:49 GMT

Sum({<ID1={"=Left(ID1,1) <> '1'"}>+<Plant=-{'A'}>} Value)

Re: If condition in set analysis

neha_shirsath — Wed, 02 Aug 2017 07:09:24 GMT

Use this-

if(WildMatch(ID,'1*'),Sum({<Plant=-{A}>}Value),sum(Value))

Re: If condition in set analysis

arvind_patil — Wed, 02 Aug 2017 07:15:22 GMT

Thanks for reply,

But I dont want to use if condition in it.

Thanks,

Arvind Patil

Re: If condition in set analysis

Anonymous — Wed, 02 Aug 2017 07:16:28 GMT

Hi Arvind,

Can you please try this:

sum({<ID-={'1*'}>}Value)

Based on your requirement, i understand that you need sum of value for the ID's other than start with 1.

Hence the output should be :

ID Plant Value

200 A 200

400 A 300

400 B 280

400 C 220

Result is 1000

Re: If condition in set analysis

effinty2112 — Wed, 02 Aug 2017 07:21:35 GMT

Hi Arvind,

Maybe:

=sum({$-<ID = {'1*'},Plant = {'A'}>}Value)

Cheers

Andrew

Re: If condition in set analysis

rahulpawarb — Wed, 02 Aug 2017 07:23:15 GMT

May be this:

//First Draft:
=Sum(If(Match(Left(ID,1),1) AND Match(Plant, 'A'), 0, Value))

Regards!

Rahul Pawar

Re: If condition in set analysis

Kushal_Chawda — Wed, 02 Aug 2017 07:57:31 GMT

why you need if condition if same is doable using set analysis? If condition is not advisable due to it's performance impact.

Re: If condition in set analysis

ankur_abhishek — Wed, 02 Aug 2017 09:25:25 GMT

=sum(Value)- Sum({<ID={"=Left(ID,1) = '1'"} ,Plant={'A'}>} Value)

Re: If condition in set analysis

jonathandienst — Wed, 02 Aug 2017 09:40:14 GMT

You need to exclude ID = 1* from the second clause of your set expression:

Sum({<ID = {'1*'}, Plant -= {"A"}> + <ID -= {'1*'}, Plant = { "*"}>} Stock)

Re: If condition in set analysis

kusumanchir — Wed, 02 Aug 2017 10:40:12 GMT

Hi Arvind,

Please try this,

sum(

{

<ID={'*'}>

(<ID={'1*'}>

<PLANT={'A'}>)

}

VALUE)

Re: If condition in set analysis

Harshal_Sanas — Mon, 11 Oct 2021 07:38:05 GMT

need to use "*" instead of '*'

=sum({<ID={"1*"},Plant-={"A"}>+<ID-={"1*"},Plant={"*"}>}Value)