topic Re: Loading Multiple QVDs into One Table in QlikView

Loading Multiple QVDs into One Table

Tue, 30 Jul 2013 13:14:38 GMT

Hi there,

I'm relatively new to Qlikview - in fact I've mostly adapted existing documents rather than developed from scratch (picking it up as I go).

I have a large QVD (about 200 columns, 2million rows) which the memory on my PC doesn't seem to be able to handle. So I've split my data into several smaller QVDs (all with the exact same 200 columns, but with only 300,000 rows in each) and am now trying to load them into one table in my Qlikview doc. I have named them similarly i.e. QVD1, QVD2, QVD3 with the aim of using a wildcard.

I am using the following syntax:

DATA:

LOAD SELECT FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD*.qvd

(qvd)

Now I've tried this in a blank document to test it, and it works fine. (i.e. it creates a table structure called "DATA" which contains all 2 million records.)

However, I am trying to load this data into a previously developed document, and when I use this syntax for the load of this particular data, it inserts the first QVD into DATA, but for each QVD after that, it seems to create a separate table structure for each one, which it names DATA-1, DATA-2, DATA-3 etc. (I think the person who developed it (who is not around any more) must have had a more powerful PC than I have as they were able to load it all in one!)

Does anyone have any idea why it might do this? I know it's a bit generic, and apologies for that, but I've spent a couple of days now trying to re-run it various ways and can't understand why it won't work when I plug it into the document. I am hoping it might be a settting somewhere that I can change, but I doubt it's that simple? If not, I am assuming it's something else in the Qlikview code that is causing it.

Just to note: I've also tried

DATA:

LOAD SELECT FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD1.qvd

(qvd);

CONCATENATE

LOAD SELECT FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD2.qvd

(qvd);

LOAD SELECT FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD3.qvd

(qvd);

etc etc...

Again, this works in test, but I get the following error message when I run this: "Internal Inconsistency, Type D, detected", which I think is a memory issue again - would I be right?

Any help would be much appreciated.

Many thanks,
Sarah

Re: Loading Multiple QVDs into One Table

khadeer — Tue, 30 Jul 2013 13:42:21 GMT

Hi,

Qlikview is basically works on In- Memory BI solution, It depends only on RAM size. Its is basically an memory error. First you loaded entire data into one QVD, Again u r splitting that data into some QVDs, Just like splitting the data year wise. Again you are concatnating those QVD's. Its not make a difference. If you want work on same take some data and work on it.

Regards,

Khadeer

Re: Loading Multiple QVDs into One Table

Tue, 30 Jul 2013 14:01:28 GMT

Hi Khadeer,

Thank you for your reply. I understood that I should be able to load this data somehow, as my colleague loaded the same amount of data to this application in the past. I am even using his old PC so I have the same amount of RAM that was used before.

I have made the relevant changes to the front-end, using the debugger to do a limited load, and this runs fine. However now when I try to load in all the data, and this is where it fails... I would love to know he reloaded it, but unfortunately he's not around to ask!

Many thanks,
Sarah

Re: Loading Multiple QVDs into One Table

jagan — Tue, 30 Jul 2013 14:08:56 GMT

Hi,

Try this script

DATA:

LOAD FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD1.qvd

(qvd);

CONCATENATE(DATA)

LOAD FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD2.qvd

(qvd);

CONCATENATE(DATA)

LOAD FIELDA, FIELDB, FIELDC, ....

FROM [QVD file path]\QVD3.qvd

(qvd);

Hope this helps you.

Regards,

Jagan.

Re: Loading Multiple QVDs into One Table

Tue, 30 Jul 2013 16:14:22 GMT

Hi Jagan,

Thank you for your reply. I've just tried your suggestion, but it unfortunately didn't work. The data still got separated into DATA-1, DATA-2 etc.

However, since I last posted I realised that when I load in the full QVD (all 2 million rows) the load was non-optimised. I am now working on optimising the load and hoping that this should help with my memory issue when I am loading the full QVD.

I post again once I've finished to let you know how I get on.

Thanks again,

Sarah

Re: Loading Multiple QVDs into One Table

Anonymous — Sun, 28 Dec 2014 13:47:22 GMT

Optimize the QVD.

Why you QVD is not optimized??? How You have generated it?

If You don't have memory to load QVD there is No solution.

Re: Loading Multiple QVDs into One Table

Anonymous — Sun, 28 Dec 2014 14:58:27 GMT

Hi,

Have you used any complex calculations while extracting data from source systems?

Extract raw data into 1st QVW with no transformations and store it to QVD(Optimized Load).

then make any transformations in 2nd QVW using above QVD.

Re: Loading Multiple QVDs into One Table

jagan — Mon, 29 Dec 2014 02:18:47 GMT

Hi,

If you are not doing any calculations and not using any conditions during load then it will be in Optimized load only.

Note: Also store only the fields which you are using in the Dashboard, this way it reduces the size of the QVD and performance would be better.

Regards,

Jagan.

Re: Loading Multiple QVDs into One Table

jagan — Mon, 29 Dec 2014 02:19:42 GMT

Hi,

Did you used Concatenate statment? And all the QVDs having same columns? Can you post your script?

Regards,

Jagan.