Conditional depending on 3 values for one Account ID

[#1]

Hello!

I have the following table:

ACCOUNT_ID	CATEGORY
001	INSPECTION
001	CONTRACT
001	SECURITY
002	CONTRACT
002	SECURITY
003	SECURITY

What I need is to define the following:

If an ACCOUNT_ID has a CATEGORY=INSPECTION (no matter if it has CONTRACT or SECURITY)

     I'd need to set a FLAG_ANOMALY as "With Anomaly"

If an ACCOUNT_ID ONLY has a CATEGORY=SECURITY (with no INSPECTION and no CONTRACT)

     I'd need to set a FLAG_ANOMALY as "No Anomaly"

So I'd have something like this:

ACCOUNT_ID	FLAG_ANOMALY
001	With Anomaly
002	With Anomaly
003	No Anomaly

(But keeping the categories for each account, for example: ACCOUNT_ID = 001 - CATEGORY = INSPECTION, CONTRACT, SECURITY)

Do you know how could I do that?

Thank you!!!

PS: I need a solution for the script.

[venkatg6759]

=if(only({$<CATEGORY={'INSPECTION'}>}ACCOUNT_ID),'With Anomaly',if(only({$<CATEGORY={'SECURITY'}>}ACCOUNT_ID),'No Anomaly'))

[alexandros17]

This is the code you need:

AAA:

LOAD * Inline [

ACCOUNT_ID, CATEGORY

001, INSPECTION

001, CONTRACT

001, SECURITY

002, CONTRACT

002, SECURITY

003, SECURITY

];

Left Join

LOAD

  ACCOUNT_ID,

  Concat(CATEGORY) as ccc,

  If(Index(Concat(CATEGORY), 'INSPECTION')>0, 'With Anomaly',

  If(Trim(Replace(Concat(CATEGORY),'SECURITY',''))='', 'No Anomaly', ''

  )

  ) as Status

Resident AAA

[#4]

Thanks but I need a solution for the script.

[alexandros17]

Take a look at my solution then ...

[#6]

Thanks! I'm trying your solution in script but I get the following:

Invalid expression

Left Join

LOAD

  ACCOUNT_ID,

   Concat(CATEGORY) as ccc,

    If(Index(Concat(CATEGORY), 'INSPECTION')>0, 'With Anomaly',

    If(Trim(Replace(Concat(CATEGORY),'SECURITY',''))='', 'No Anomaly', ''

   )

   ) as Status

Resident AAA

[alexandros17]

Substitute AAA with your table name containing ACCOUNT_ID and CATEGORY

[#8]

I tried it like this this:

AAA:

LOAD * Inline [

ACCOUNT_ID, CATEGORY

001, INSPECTION

001, CONTRACT

001, SECURITY

002, CONTRACT

002, SECURITY

003, SECURITY

];

Left Join (AAA)

LOAD

  ACCOUNT_ID,

  Concat(CATEGORY) as ccc,

    If(Index(Concat(CATEGORY), 'INSPECTION')>0, 'With Anomaly',

    If(Trim(Replace(Concat(CATEGORY),'SECURITY',''))='', 'No Anomaly', ''

   )

   ) as Status

Resident AAA;

But the table is AAA in this example

[alexandros17 · Accepted solution]

I'm really sorry, doing cut and paste I missed a part ...

here it is the complete script

AAA:

LOAD * Inline [

ACCOUNT_ID, CATEGORY

001, INSPECTION

001, CONTRACT

001, SECURITY

002, CONTRACT

002, SECURITY

003, SECURITY

];

Left Join

LOAD

  ACCOUNT_ID,

  Concat(CATEGORY) as ccc,

  If(Index(Concat(CATEGORY), 'INSPECTION')>0, 'With Anomaly',

  If(Trim(Replace(Concat(CATEGORY),'SECURITY',''))='', 'No Anomaly', ''

  )

  ) as Status

Resident AAA

Group By ACCOUNT_ID;

The group by was the part I forgot

[#10]

No problem! Thank you very much. I've tried it and it works!