loading a field with distinct values

[Anonymous]

Hi,

I've got a big data tableX which has among others a field 'Brands'. There are actually 10 distinct values in the field 'Brand'. But the number  of rows in the table is 1000.

My task is to load a separate tableY with the field 'Brands2' form the resident tableX so that ONLY distinct values could be included in the field 'Brands2'.

My script is as follows:

tableY:

load Brands as Brands2

resident tableX;

This script must be wrong because when I check the number of the values in the filed 'Brands' on the forefront it indicates 1000. But it must be 10.

How can I load only distinct values?

Thank you in advance,

Larisa

[maximiliano_vel · Accepted solution]

tableY:

load DISTINCT

Brands as Brands2

resident tableX;

And the check in table viewer how many rows it loaded.

Rgds

[Anonymous]

Thank you!

[marcus_sommer]

A faster alternatively would be to query the field which is always distinct instead the whole table:

LOAD dual(FieldValue('Brands', recno()), FieldValue('Brands', recno())) as Brands2

AUTOGENERATE FieldValueCount('Brands');

By 1000 records it's not important but if you have millions of records you will notice the differences.

- Marcus