If - Separating fields by its type

[markgraham123]

Hi,

I was trying to separate the IDs based on their type.

The Type is associated with only Names. So, i wanna separate IDs based on the below logic:

If any of the ID has Type A, then it falls under the A Category even though if it has a second name with B Category in it.

ID falls into the Category B, only if it has name with just Type 'B'.

ID,	Name,	Type
abcd,	123,	A
abcd,	456,	B
efgh,	789,	A
ijkl,	1011,	B
mnop,	1213,	A
qrst,	1415,	B

O/P:

A Category IDs:

abcd,

efgh,

mnop

B Category IDs:

ijkl,

qrst

Any help is highly appreciated.

[markgraham123]

Hi Gysbert,

I tried the above code as it is very optimized. But not working.

Can you please help me in getting the code optimized. as the below code is too long.

Table1:

LOAD * Inline

[

ID,    Name, Type

abcd, 123, A

abcd, 456, B

efgh, 789, A

ijkl, 1011,   B

mnop, 1213, A

qrst, 1415, B

];

Temp1:

LOAD *, If(Type='A', ID) as [A IDs]

Resident

Table1;

DROP Table Table1;

Left Join

Temp2:

LOAD ID,

  ID as [B Category IDs],

  Name,

  Type

Resident Temp1

Where not Exists([A IDs], ID);