topic Re: Data Loss after DROP FIELD statement in QlikView

Data Loss after DROP FIELD statement

Tue, 03 May 2016 12:46:39 GMT

Dear Community,

has anyone experienced the loss of a record from a table after dropping a field (unique identifier) that is the unique difference between 2 records??

When trying to simulate this case QV seems to work in the proper way!!

Enclosed is the simplified example of the case (however it works perfectly well!) while irl in our App we lose a record. When undoing the drop of the field everything worked fine again...

Best regards

Re: Data Loss after DROP FIELD statement

sunny_talwar — Tue, 03 May 2016 12:48:38 GMT

I don't see any data loss. There are 10 observations in Test table and 10 observations in Test1 Table

Re: Data Loss after DROP FIELD statement

robert_mika — Tue, 03 May 2016 13:37:49 GMT

Can you post more information about the lost record?

Re: Data Loss after DROP FIELD statement

Wed, 04 May 2016 07:32:52 GMT

Hi Sunny, that's indeed the case as I already mentioned in mij explanation. When shaping our datamodel for a real live DataModel/App this occurred.

Re: Data Loss after DROP FIELD statement

Wed, 04 May 2016 07:37:54 GMT

Hi Robert,

unfortunately it's not possible to post the complete App here due to complexity, security and size.

That's why I asked for similar experiences. I hoped for answers that this could occur in relation to certain versions of QV or....??

Looks like we have to do more elaborate testing and trying!!

Thx for your interest.

BR P.

Re: Data Loss after DROP FIELD statement

sunny_talwar — Wed, 04 May 2016 07:41:52 GMT

Have you checked this link:

Preparing examples for Upload - Reduction and Data Scrambling

Re: Data Loss after DROP FIELD statement

avinashelite — Wed, 04 May 2016 07:43:37 GMT

could you please share the sample data set and demonstrate the loss of data ?? so that we can identify the issue..

Re: Data Loss after DROP FIELD statement

jonathandienst — Wed, 04 May 2016 08:01:47 GMT

First check that this is truely the case. Views like the Tablebox only show the distinct rows, but they may still exist as two separate rows.

Having checked that, then I suspect that is due to the way the distinct keyword works in QV. If you have any load step for the table (including joins), then the distinct attribute is applied to the table from that point onward. You are dropping the field that is keeping the records distinct. Since they are no longer distinct and the table has the distinct attribute (because an earlier LOAD had the distinct keyword), the "duplicate" row will be dropped.

This applies even on a join, so

Left Join (Table1)

LOAD distinct * Resident Table2;

then Table1 will be marked as distinct and duplicate records will be dropped.

See here:

DISTINCT can be deceiving - The Qlik Fix! The Qlik Fix!

Re: Data Loss after DROP FIELD statement

Wed, 04 May 2016 08:05:24 GMT

Hi Robert,

the data was more or less what's in the test QVW. But in larger quantities (ca 20.000.000 records).

Hope this is sufficient info.

Re: Data Loss after DROP FIELD statement

robert_mika — Wed, 04 May 2016 08:07:36 GMT

Jonathan have explained even more in detail what I had want to say.

Re: Data Loss after DROP FIELD statement

Wed, 04 May 2016 08:16:59 GMT

Hi Jonathan,

thx for your post.

We're positive the record has vanished (checked with statistics box on the amount with the correct selection).

Indeed there are preceding joins with the distinct keyword on the table. The keyfields used for the join however are not the ones which we drop later on. That makes a difference or is your explanation still valid?

So as a test it would be good to copy the complete table in the script and do a similar drop of the field and than check the results?

Thx for your post

Re: Data Loss after DROP FIELD statement

Anonymous — Wed, 04 May 2016 08:29:31 GMT

concerate the two tables than solve your problem

Re: Data Loss after DROP FIELD statement

jonathandienst — Wed, 04 May 2016 08:38:45 GMT

>>Indeed there are preceding joins with the distinct keyword on the table. The keyfields used for the join however are not the ones which we drop later on. That makes a difference or is your explanation still valid?

Yes. The attribute is set for the table as a whole, so it looks at the entire record, regardless of when or how fields are added or removed.

>>So as a test it would be good to copy the complete table in the script and do a similar drop of the field and than check the results?

I am not sure about that. I don't think the distinct attribute persists when copied to a qvd and it certainly doesn't when copied to text/excel. I don't know about a binary load. But in general, there needs to be at least one load with distinct targetting the table to set the attribute.

Re: Data Loss after DROP FIELD statement

enriquem — Fri, 03 Mar 2017 09:56:01 GMT

Hi,

I found the same error, and it's occurs when you do a join between 2 tables and the you drop a field in the table. If some rows have the same data, this will disappear in the 'drop field...' sentence by the pre join. I don't know why occurs, but you can solve it without the 'drop field...' sentence and using an post load with all the field of the previous table without the field you wanted to delete.

Hope this help!