Creating Multiple Account Flags

[Anonymous]

Table:

Load * inline

[

ID, Type

1, A

1, B

2, A

2, B

2, E

3, D

3, B

3, C

];

FlagTable:

LOAD ID,

1 as Flag1,

    IF(MATCH(UPPER(Type), 'A'), 1) as Flag2,

    IF(MATCH(UPPER(Type), 'B', 'C'),  1) as Flag3

RESIDENT Table;

DROP TABLE Table;

EXIT SCRIPT;

Getting:

WANT:

ID	Flag1	Flag2	Flag3
1	1	1	1
2	1	1	1
3	1		1

Any suggestions?

[OmarBenSalem · Accepted solution]

FlagTable:

load max(Flag3) as Flag3 , max(Flag1) as Flag1, max(Flag2) as Flag2 , ID group by ID;

LOAD ID,

1 as Flag1,

    IF(MATCH(UPPER(Type), 'A'), 1) as Flag2,

    IF(MATCH(UPPER(Type), 'B', 'C'),  1) as Flag3

RESIDENT Table;

DROP TABLE Table;

EXIT SCRIPT;

[OmarBenSalem]

result: