Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
=if(only({$<CATEGORY={'INSPECTION'}>}ACCOUNT_ID),'With Anomaly',if(only({$<CATEGORY={'SECURITY'}>}ACCOUNT_ID),'No Anomaly'))
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
Thanks but I need a solution for the script.
Take a look at my solution then ...
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
Substitute AAA with your table name containing ACCOUNT_ID and CATEGORY
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
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
No problem! Thank you very much. I've tried it and it works!