loading Split qvds giving different results to single qvd with same data

[matthewp]

Ok i had a simple extract script which was:

INFOTABLE:

Load

  *;

SQL

SELECT *

FROM infotable

Store  INFOTABLE into $(vDetailsQVDDirectory)\INFOTABLE.qvd;

Drop table INFOTABLE;

exit script;

This extract was then used in a load script:

INFOTAB:

LOAD *

FROM

[$(vDetailsQVDDirectory)\INFOTABLE.qvd]

(qvd);

And loaded everything.

I then wanted to split the load for schedule efficiency and archive purposes to two separate extract scripts:

INFOTABLEPY:

Load

  *;

SQL

SELECT *

FROM infotable

WHERE YEAR != '2017';

Store  INFOTABLEPY into $(vDetailsQVDDirectory)\INFOTABLEPY.qvd;

Drop table INFOTABLEPY;

and

INFOTABLECY:

Load

  *;

SQL

SELECT *

FROM infotable

WHERE YEAR = '2017';

Store  INFOTABLECY into $(vDetailsQVDDirectory)\INFOTABLECY.qvd;

Drop table INFOTABLECY;

These are then loaded by using a load script with a * to pick them both up:

INFOTAB:

LOAD *

FROM

[$(vDetailsQVDDirectory)\INFOTABLE*.qvd]

(qvd);

However when loaded together the new way has less rows (and missing data) than the old way.

Why is this happening, I am not limiting any data?

Image to help people:

[matthewp]

2013	78978	487830022.49
2014	172114	548492788.36
2017	12447	7028203.87
2011	186	54108027.26
2015	156306	497404589.49
2016	186531	514705015.2
2012	19213	518833421.51

[swuehl]

Still it could be helpful if you could identify the missing rows.

For example, are they missing in both 2017 and previous years or only in one of the extracts?

Besides this, it could be helpful to check the number of rows extracted from the DB in the three SELECT statements.

Does the number of rows already differ at that point or only after storing / loading from QVD?

[matthewp]

Thats a valid point and a good way to dissect it!

FROM Qlikview:

OLD

PRE 2017 - 2017

460715       9645

NEW

PRE 2017 - 2017

452826        9654

SIDE NOTE: 9654 and 9645 are not typos

[swuehl]

Sorry, what exactly do these number show?

I am a bit confused to see two numbers for OLD.

[matthewp]

OLD is the old way i loaded the data with a single extract and a single load

NEW is the new way i loaded the data with two extracts and a single load

[swuehl]

But if OLD is the single extract, how do you get the two numbers for the row count?

OLD

PRE 2017 - 2017

460715      9645

[matthewp]

extract it to excel, put a filter on for 2017, count them, put a filter on for not 2017, count them

[matthewp]

Also added an illustration to the question to visually show what I am doing

[swuehl]

Now you are adding another tool with a different filter on the data...

I was just asking to identify the first occurence of discrepancy between your approaches.

a) It already happens when extracting from DB --> Compare first SELECT, number of records, with 2nd and 3rd SELECT, sum of number of records retrieved.

b) it happens somewhere within Qlik, storing / loading the data

[matthewp · Accepted solution]

Got this working with correct data by defining both qvds with their own loads:

INFOTAB:

LOAD *

FROM

[$(vDetailsQVDDirectory)\INFOTABLEPY.qvd]

(qvd);

LOAD *

FROM

[$(vDetailsQVDDirectory)\INFOTABLECY.qvd]

(qvd);