topic Re: If - Separating fields by its type in QlikView

If - Separating fields by its type

markgraham123 — Tue, 09 Feb 2016 17:19:14 GMT

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.

Re: If - Separating fields by its type

sunny_talwar — Tue, 09 Feb 2016 17:27:01 GMT

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;

Re: If - Separating fields by its type

Gysbert_Wassenaar — Tue, 09 Feb 2016 17:29:28 GMT

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.

Re: If - Separating fields by its type

markgraham123 — Tue, 09 Feb 2016 17:35:08 GMT

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.

Re: If - Separating fields by its type

markgraham123 — Tue, 09 Feb 2016 17:48:46 GMT

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

sunny_talwar — Tue, 09 Feb 2016 17:53:03 GMT

Sure, if it gets you what you want

Re: If - Separating fields by its type

sunny_talwar — Tue, 09 Feb 2016 17:53:33 GMT

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

Re: If - Separating fields by its type

markgraham123 — Tue, 09 Feb 2016 17:54:07 GMT

Thank you verymuch brother.

Re: If - Separating fields by its type

markgraham123 — Tue, 09 Feb 2016 17:55:30 GMT

But its not working....

Let me try it again

Re: If - Separating fields by its type

sunny_talwar — Tue, 09 Feb 2016 17:57:08 GMT

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

Re: If - Separating fields by its type

markgraham123 — Tue, 09 Feb 2016 17:59:10 GMT

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