Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
markgraham123
Valued Contributor

create a single field of values using Not Exists

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!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: create a single field of values using Not Exists

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.

View solution in original post

15 Replies
Highlighted

Re: create a single field of values using Not Exists

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

Capture.PNG

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

Highlighted
markgraham123
Valued Contributor

Re: create a single field of values using Not Exists

Req. O/P:

Category SKU

A             1

A             2

A             3

B             4

C             5

Highlighted
MVP
MVP

Re: create a single field of values using Not Exists

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

Highlighted

Re: create a single field of values using Not Exists

May be like attached

Highlighted
markgraham123
Valued Contributor

Re: create a single field of values using Not Exists

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?

Highlighted

Re: create a single field of values using Not Exists

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

Highlighted
markgraham123
Valued Contributor

Re: create a single field of values using Not Exists

Sunny,

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

Sorry for the confusion

Highlighted
markgraham123
Valued Contributor

Re: create a single field of values using Not Exists

Stefan,

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

Highlighted
MVP
MVP

Re: create a single field of values using Not Exists

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?