topic Re: loading a field with distinct values in QlikView

loading a field with distinct values

Anonymous — Sat, 15 Aug 2015 15:37:13 GMT

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

Re: loading a field with distinct values

maximiliano_vel — Sat, 15 Aug 2015 15:45:41 GMT

tableY:

load DISTINCT

Brands as Brands2

resident tableX;

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

Rgds

Re: loading a field with distinct values

Anonymous — Sat, 15 Aug 2015 16:04:39 GMT

Thank you!

Re: loading a field with distinct values

marcus_sommer — Sat, 15 Aug 2015 16:16:25 GMT

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