How to create Set Analysis in combination with If statement?

[mrichman]

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.

[sunny_talwar · Accepted solution]

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)

or

Sum(MatchFlag)

[olivierrobin]

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

t:

noconcatenate

load distinct Customer where Type1=Type2

resident Data;

[mrichman]

Hi Sunny,

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

Thanks in advance.

[sunny_talwar]

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

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

[mrichman]

Thanks Sunny!