create a single field of values using Not Exists

[markgraham123]

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!

[sunny_talwar]

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

[swuehl]

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;

[markgraham123]

Stefan,

I'm not a high skilled programmer.

I learnt a thing from this discussion about Exists.

[swuehl]

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?

[markgraham123]

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?

[swuehl · Accepted solution]

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.