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.

[sunny_talwar · Accepted solution]

May be this:

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 ID,

  Name

Resident Table1

Where Type = 'A';

Left Join (Temp1)

LOAD ID as [A Category IDs],

  Name,

  Type

Resident Table1;

Temp2:

LOAD ID,

  ID as [B Category IDs],

  Name,

  Type

Resident Table1

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

DROP Table Table1;

Final:

NoConcatenate

LOAD *

Resident Temp1;

Concatenate(Final)

LOAD *

Resident Temp2;

DROP Table Temp1, Temp2;

[Gysbert_Wassenaar]

Something like this:

LOAD ID, Name, Type, If(Exists(ID), 'A',Type) as Category INLINE [

ID,    Name, Type

abcd, 123, A

abcd, 456, B

efgh, 789, A

ijkl, 1011, B

mnop, 1213, A

qrst, 1415, B

];

The inline load is just an example. You can use another type of load just as well.

[markgraham123]

Sunny,

I tried this approach.

As the data is huge and this approach is taking very long time, i was looking for some optimized method.

Can u help pls.

[markgraham123]

Sunny,

I just skipped few tables from your code in between.

Does this look good?

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);

[sunny_talwar]

Sure, if it gets you what you want

[sunny_talwar]

Have you looked at Gysbert's code? Looks pretty short and sweet as well. Might want to look at his suggestion as well.

[markgraham123]

Thank you verymuch brother.

[markgraham123]

But its not working....

Let me try it again

[sunny_talwar]

I haven't tried it, so don't know, but he might be able to fine tune it if you seek his advice