Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

markgraham123
Contributor II

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.

Tags (2)
1 Solution

Accepted Solutions

Re: If - Separating fields by its type

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

10 Replies

Re: If - Separating fields by its type

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

Re: If - Separating fields by its type

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
Contributor II

Re: If - Separating fields by its type

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
Contributor II

Re: If - Separating fields by its type

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

Re: If - Separating fields by its type

Sure, if it gets you what you want

Re: If - Separating fields by its type

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

markgraham123
Contributor II

Re: If - Separating fields by its type

Thank you verymuch brother.

markgraham123
Contributor II

Re: If - Separating fields by its type

But its not working....

Let me try it again

Re: If - Separating fields by its type

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

Community Browser