topic Re: distinct keyword in script and explanation in QlikView

distinct keyword in script and explanation

Tue, 10 Dec 2013 22:01:57 GMT

I have the following in my script, where I have 2 records (in bold) that are identical without the DISTINCT keyword. When I create a output table on the front end to load the whole table, why does the record only show once eventhough I dont have the DISTINCT keyword in my script.

Sales:

LOAD

INLINE [

DateID, StoreID, ProductID, SaleQty, SaleValue

1, 1, 1, 2, 23

1, 1, 2, 4, 24

2, 1, 1, 4, 33

2, 1, 2, 3, 28

1, 2, 1, 2, 21

1, 2, 2, 4, 30

1, 1, 1, 2, 23

1, 1, 1, 2, 24

];

Thanks,

Trung

Re: distinct keyword in script and explanation

rbecher — Tue, 10 Dec 2013 22:12:16 GMT

Hi,

this is how the table object works. You can generate a unique field with RecNo() or RowNo() during LOAD to achieve this (if needed at all)..

- Ralf

Re: distinct keyword in script and explanation

Tue, 10 Dec 2013 22:26:55 GMT

Hi Ralf:

Thank you for the clarification. The table object removes the duplicate record, however without the DISTINCT keyword, the record is still in the data model. Only with the DISTINCT keyword, does it only load the first record and not the duplicate record.

Thank you,

Trung

Re: distinct keyword in script and explanation

Tue, 10 Dec 2013 22:35:06 GMT

Use a table box if you want to see your original data (2 bold rows).

Re: distinct keyword in script and explanation

rbecher — Tue, 10 Dec 2013 22:49:49 GMT

If you want to remove the distinct records from the table (data model) you have to

LOAD DISTINCT ...

Re: distinct keyword in script and explanation

Tue, 10 Dec 2013 23:04:01 GMT

Hi Oswaldo:

When I use a table box, I do not see the 2 bold rows. I only see 1 of the duplicate rows. It seems like the "table box" object removes the duplicate record

Trung

Re: distinct keyword in script and explanation

Tue, 10 Dec 2013 23:18:27 GMT

Hello Trung,

Well as the other guys said, you have to use a kind of qualifier field, may be something like this:

Sales:

LOAD * INLINE [

DateID, StoreID, ProductID, SaleQty, SaleValue,Row

1, 1, 1, 2, 23,1

1, 1, 2, 4, 24,2

2, 1, 1, 4, 33,3

2, 1, 2, 3, 28,4

1, 2, 1, 2, 21,5

1, 2, 2, 4, 30,6

1, 1, 1, 2, 23,7

1, 1, 1, 2, 24,8

];