Flag the values by ID

[karan_kn]

Please help us to flag the ID's by 'A'.  If the 'A' come alone, flag as Single, If the 'A' comes with other values like 'B' 'C', flag as multiple.

Ex: ID=1 has multiple values in field

      ID=2 has only 'A' in field

LOAD * INLINE [

    ID, Field

    1, A

    1, B

    1, C

    2, A

    2, A

    3, A

    3, A

    3, B

    4, A

    5, A

    5, B

    5, C

    6, A

    6, A

];

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    ID, Field

    1, A

    1, B

    1, C

    2, A

    2, A

    3, A

    3, A

    3, B

    4, A

    5, A

    5, B

    5, C

    6, A

    6, A

];

Left Join (Table)

LOAD ID,

If(Count(DISTINCT Field) = 1, 'Single', 'Multiple') as Flag

Resident Table

Group By ID;

[karan_kn]

Hi Sunny, Thanks for your reply, my requirement is the Field contains 'A' only it should consider, rest of the field values can ignore.

Table:

LOAD * INLINE [

    ID, Field

    1, A

    1, B

    1, C

    2, A

    2, A

    3, A

    3, A

    3, B

    4, A

    5, A

    5, B

    5, C

    6, A

    6, A

7, B

8, C

8, C

];

[sunny_talwar]

May be this

Table:

LOAD * INLINE [

    ID, Field

    1, A

    1, B

    1, C

    2, A

    2, A

    3, A

    3, A

    3, B

    4, A

    5, A

    5, B

    5, C

    6, A

    6, A

    7, B

    8, C

    8, C

];

Left Join (Table)

LOAD ID,

1 as TempFlag

Resident Table

Where Field = 'A';

Left Join (Table)

LOAD ID,

If(Count(DISTINCT Field) = 1, 'Single', 'Multiple') as Flag

Resident Table

Where TempFlag = 1

Group By ID;

[MarcoWedel · Accepted solution]

Hi,

another solution might be:

table1:
LOAD * INLINE [
    ID, Field
    1, A
    1, B
    1, C
    2, A
    2, A
    3, A
    3, A
    3, B
    4, A
    5, A
    5, B
    5, C
    6, A
    6, A
    7, B
    8, C
    8, C
];

Join
LOAD ID,
    If(Min(Field='A'),If(Count(DISTINCT Field)>1,'Multiple','Single')) as Flag   
Resident table1
Group By ID;

hope this helps

regards

Marco