topic Re: How to create Set Analysis in combination with If statement? in QlikView

How to create Set Analysis in combination with If statement?

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

How can I best approach the following issue. I have two fields, which likely have the same values (type1 and type2) and want to count based on customer ID.

Example:

Data:

LOAD * inline [

Customer|Type1|Type2

Astrida|TV|Radio

Astrida|TV|Ref

Astrida|TV|TV

] (delimiter is '|');

In word: only count Customer if Type1 and Type2 have the equal field, and ignore all rows which do not comply with the formula.

Thanks in advance.

Re: How to create Set Analysis in combination with If statement?

sunny_talwar — Wed, 30 May 2018 14:32:28 GMT

May be this

Count(DISTINCT If(Type1 = Type2, Customer))

Or you can create a flag in the script like this

Data:

LOAD *,

If(Type1 = Type2, 1, 0) as MatchFlag;

LOAD * inline [

Customer|Type1|Type2

Astrida|TV|Radio

Astrida|TV|Ref

Astrida|TV|TV

] (delimiter is '|');

and then this expression

Count(DISTINCT {<MatchFlag = {1}>} Customer)

Sum(MatchFlag)

Re: How to create Set Analysis in combination with If statement?

olivierrobin — Wed, 30 May 2018 14:33:10 GMT

hello

do you want just to load the rows depending on your croteria ?

if yes, add where Type1=Type2

if you want to count after loading, you can add something like

noconcatenate

load distinct Customer where Type1=Type2

resident Data;

Re: How to create Set Analysis in combination with If statement?

mrichman — Wed, 30 May 2018 15:32:23 GMT

Hi Sunny,

Thanks for the prompt reply. What if field Type1 and Type2 are case sensitive for example TV vs Tv?

Thanks in advance.

Re: How to create Set Analysis in combination with If statement?

sunny_talwar — Wed, 30 May 2018 16:16:50 GMT

Then make them insensitive using Upper(), Lower(), or Capitalize()

Count(DISTINCT If(Lower(Type1) = Lower(Type2), Customer))

Re: How to create Set Analysis in combination with If statement?

mrichman — Wed, 30 May 2018 18:06:25 GMT

Thanks Sunny!