Resident Load and Loading from QVD giving different row count

[Subhasmita]

Hi Experts,

I am seeing difference in row counts when loading data directly from QVD and loading the same data as resident load. 

Ex:

Temp_Table:

Load a,b,c from xyz.qvd;

Table:

noconcatenate

Load * Resident Temp_Table;

so from the above example, Table and Temp_Table both are showing different row count, however it should be same as i don't have any where condition. Temp_Table shows few less row count.

any idea why this is happening? Please advise.

Thanks!

SK

[Benoit_C]

Hi @Subhasmita,

Maybe it's due to the automatic concatenation of tables in Qlik Sense. When you load data from a QVD file and then perform a resident load, Qlik Sense might concatenate the tables if the field names are the same. This can lead to a difference in row counts between the two tables.

You could try to use the NOCONCATENATE keyword before the second load statement, as you've already done. However, you can also ensure that there are no other tables in your script with the same field names as the Temp_Table and Table. If there are, Qlik Sense might still concatenate the tables, leading to the difference in row counts.

Regards,
Benoit

[Subhasmita]

thanks for your analysis on this issue!!

For the testing purpose , i only loaded that table from QVD and then as resident load with noconcatenate command. there is no other data in the model. I am seeing few less records from the resident load compared to QVD load.

If concatenation happens, we would get doubled up the row counts, right? Please advise.

[Benoit_C]

@Subhasmita Do you have synthetic keys or circular references in your data model ?

[Subhasmita]

No, as mentioned i just have only 1 table in my data model which i testing. 

1. I am loading from QVD without any condition
2. Doing resident with noconcatenate and without any additional conditions
3. Dropping the earlier table from QVD

First table gives more rows(~58,876,986) and second gives only few less rows(~58,786,531) when checked.

[Benoit_C · Accepted solution]

@Subhasmita have you tried to compare the both table ?
You can create a third table that contains the records present in the first table (loaded from QVD) but not in the second table (loaded using Resident Load). This will help you identify any differences in the data that might be causing the discrepancy in row count.

Ex:

Temp_Table:
LOAD a, b, c FROM xyz.qvd;
 

Table:
NOCONCATENATE
LOAD * RESIDENT Temp_Table;
 

DROP TABLE Temp_Table;
 

Difference_Table:
LOAD a, b, c
RESIDENT Table
WHERE NOT EXISTS(a, b, c);

In this script, the Difference_Table will contain the records that are present in the Table but not in the Temp_Table. You can then check the data in the Difference_Table to identify any patterns or issues that might be causing the discrepancy in row count.

[Subhasmita]

thanks for your suggestion! I will surely try the above and confirm on the outcome.

Thanks again!

[Digvijay_Singh]

Few observations as per my understanding - 

1. DROP TABLE should be at the end, else we would have just one table before creating difference-table.

2. resident load table has fewer rows so I think difference_table should be created from Temp_Table instead of Table.

But the problem looks quite weird, if no other code is involved, difference of records is quite surprising. Can you try reducing records from QVD load using where clause and see the difference? 58 Million is huge volume so its worth checking with less volume in case something is happening behind the scene.

[barnabyd]

Another possibility is that there are duplicate rows in the QVD.

Where are you reading the row counts from?

[MayilVahanan]

HI

Try like below and check once

noconcatenate

Load *, rowno() as rownum, 1 as rowcount Resident Temp_Table;