topic Re: create a single field of values using Not Exists in QlikView

create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 12:31:39 GMT

Hi,

I have created a document using where not exists to get distinct categories of records. But it creates 3 field.

Can someone please help me create all three categories in a single field instead of three individual fields?

Any help is highly appreciated.

Current situation: Catogory A, B, and C are in differernt fields,

It woul dbe great to see it in a single field.

Thanks!

Re: create a single field of values using Not Exists

sunny_talwar — Thu, 27 Apr 2017 12:34:15 GMT

How do you want your final table to look like? Currently it looks like this after your reload

What do you expect to see differently in the above image?

Re: create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 12:36:28 GMT

Req. O/P:

Category SKU

A 1

A 2

A 3

B 4

C 5

Re: create a single field of values using Not Exists

swuehl — Thu, 27 Apr 2017 12:40:57 GMT

Just this?

Table1:

LOAD * Inline

[

SKU, Category

1, A

1, B

1, C

2, A

2, B

3, A

3, B

3, C

4, B

5, C

] WHERE NOT EXISTS(SKU);

Re: create a single field of values using Not Exists

sunny_talwar — Thu, 27 Apr 2017 12:44:32 GMT

May be like attached

Re: create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 12:54:36 GMT

This is what I'm exactyl looking for Sunny.

But as @stefanWuhl mentioned, can't we just use Not Exists (SKU) in the where statement?

Re: create a single field of values using Not Exists

sunny_talwar — Thu, 27 Apr 2017 13:07:17 GMT

Have you tried Stefan's script? It isn't working?

Re: create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 13:10:09 GMT

Sunny,

I just tried the same in original req. it didn't work.

Sorry for the confusion

Re: create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 13:10:43 GMT

Stefan,

It is not working. Let me put the result what i'm seeing.

Re: create a single field of values using Not Exists

swuehl — Thu, 27 Apr 2017 13:11:19 GMT

To make my script work, you shouldn't load SKU field before running my table load.

I think you know how exists() work? Checking an input value against a symbol table containing all field values encountered so far in your load script run?

Re: create a single field of values using Not Exists

sunny_talwar — Thu, 27 Apr 2017 13:11:52 GMT

May be you need to sort the data before you can do this

Re: create a single field of values using Not Exists

swuehl — Thu, 27 Apr 2017 13:15:11 GMT

For example, if you load your table as input and you want to keep it unchanged, you need to rename SKU when performing a resident load and check using exists() with two argument version:

Table1:

LOAD * Inline

[

SKU, Category

1, A

1, B

1, C

2, A

2, B

3, A

3, B

3, C

4, B

5, C

];

Table2:

LOAD Category, SKU as Dummy RESIDENT Table1

WHERE NOT EXISTS(Dummy,SKU);

exit script;

Re: create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 13:18:40 GMT

Stefan,

I'm not a high skilled programmer.

I learnt a thing from this discussion about Exists.

Re: create a single field of values using Not Exists

swuehl — Thu, 27 Apr 2017 13:22:43 GMT

No problem.

Have a look at

Symbol Tables and Bit-Stuffed Pointers

It's important to know that symbol tables are created adding symbols when they first appear in your data source, i.e. in so called load order (so Sunny is right, that the order of your input records determine the resulting table with your distinct SKU).

Does it work now?

Re: create a single field of values using Not Exists

markgraham123 — Thu, 27 Apr 2017 14:02:23 GMT

Stefan,

This worked.

Now, i'm trying to order it by Category B, C, and A.

So, the SKU first gets filtered by Category B if it is present in Category A or Category C.

We do this by using Order by in the previous load right?

Re: create a single field of values using Not Exists

swuehl — Thu, 27 Apr 2017 14:22:08 GMT

Not sure if I understand your requirement correctly, but yes, the input table needs to be sorted properly to make this approach work. If your sort order is a little uncommon (i.e. default ORDER BY FIELD ASC/DESC does not work), you may need to map a sort order to Category and sort by this field.