topic Conditional depending on 3 values for one Account ID in QlikView

Conditional depending on 3 values for one Account ID

Wed, 11 Jun 2014 13:42:22 GMT

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.

Re: Conditional depending on 3 values for one Account ID

venkatg6759 — Wed, 11 Jun 2014 13:52:39 GMT

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

Re: Conditional depending on 3 values for one Account ID

alexandros17 — Wed, 11 Jun 2014 13:55:01 GMT

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

Re: Conditional depending on 3 values for one Account ID

Wed, 11 Jun 2014 13:55:39 GMT

Thanks but I need a solution for the script.

Re: Conditional depending on 3 values for one Account ID

alexandros17 — Wed, 11 Jun 2014 14:06:57 GMT

Take a look at my solution then ...

Re: Conditional depending on 3 values for one Account ID

Wed, 11 Jun 2014 14:43:10 GMT

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

Re: Conditional depending on 3 values for one Account ID

alexandros17 — Wed, 11 Jun 2014 14:44:28 GMT

Substitute AAA with your table name containing ACCOUNT_ID and CATEGORY

Re: Conditional depending on 3 values for one Account ID

Wed, 11 Jun 2014 14:47:15 GMT

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

Re: Conditional depending on 3 values for one Account ID

alexandros17 — Wed, 11 Jun 2014 14:50:31 GMT

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

Re: Conditional depending on 3 values for one Account ID

Wed, 11 Jun 2014 15:08:00 GMT

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