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:

[Anil_Babu_Samineni]

Check this part

What is your intend to show in where condition

You may need something like below with semicolon ended

Where YEAR <> '2017';

And you used Exit script for each load. Why? And finally what is the issue you are facing here

[matthewp]

semi colons in and exit script out, my commented out parts didn't carry over, ignore the exit script.

my problem is

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?

[Anonymous]

Since the filter is on Year, check the db tables for null values in the Year field.

[matthewp]

Just ran the code in SQL developer

SELECT *

FROM infotable

WHERE YEAR IS NULL

and returned no data so i cant be that

[Anonymous]

Just to rule out the Year filter issue try

SELECT distinct YEAR

FROM infotable;

To see for any incosistencies

[swuehl]

Can you specify how these missing rows and missing data look like? This could give us a hint what's going on.

[matthewp]

Good idea! But unfortunately nothing inconsistent:

7 results

2013

2014

2017

2011

2015

2016

2012

[matthewp]

Nothing obvious, its 470,000 rows vs 462,000 rows

the ones that are missing have no spurious data

[Anonymous]

A last try on the year field:

Do a group by year and sum one of the values and do a count of one of the other fields if possible

SELECT YEAR, count(fieldname), sum(Sales)

FROM infotable

GROUP BY YEAR;