Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

If - Separating fields by its type

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.

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

10 Replies
sunny_talwar

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;


Capture.PNG

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.


talk is cheap, supply exceeds demand
markgraham123
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Thank you verymuch brother.

markgraham123
Specialist
Specialist
Author

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