topic Add a column to existing QVD in QlikView

Add a column to existing QVD

Thu, 13 May 2010 22:49:59 GMT

Below is my script to load data from a QVD i created before hand. However i would like to add another column in this script and all the values in this new column will be the same.

So i need to add another column called FUND and it should be populated with SPRS for all the values.

Directory;

PCPCapTable:

LOAD

AffPdExt as DateofService

FROM

qvd\PCPCapTableMCRProject.qvd

(qvd);

Thanks

Krishan

Add a column to existing QVD

Thu, 13 May 2010 22:52:29 GMT

Try with this:

LOAD

AffPdExt as DateofService,

FUND AS SPRS

FROM

qvd\PCPCapTableMCRProject.qvd

(qvd);

Regards,

Re:Add a column to existing QVD

Thu, 13 May 2010 23:00:40 GMT

Unfortunetly that did not work. It errors out.

Add a column to existing QVD

Thu, 13 May 2010 23:06:21 GMT

Share you application. I can help you.

Regards,

Re:Add a column to existing QVD

Thu, 13 May 2010 23:14:08 GMT

For security reasons i cannot but the script is pretty simple. I just need to add the extra column.

Thanks.

Re:Add a column to existing QVD

johnw — Thu, 13 May 2010 23:39:32 GMT

Like this? Or did you want to put the column IN the QVD itself, not just in your application?

Directory;
PCPCapTable:
LOAD
AffPdExt as DateofService
,'SPRS' as FUND
FROM qvd\PCPCapTableMCRProject.qvd (qvd);

Re:Add a column to existing QVD

Thu, 13 May 2010 23:49:13 GMT

Thank you this worked.

Initially i tried this but i had the "comma" after the word date of service instead of at the beginning of the next line. Weird how that works.

Thanks again.

Re:Add a column to existing QVD

johnw — Thu, 13 May 2010 23:56:00 GMT

The position of the comma actually shouldn't make any difference, so maybe it was just a typo or something. Anyway, glad you got it working now.

Add a column to existing QVD

Mon, 01 Aug 2011 15:45:07 GMT

I have daily QVD files for 3 years. I need to add a couple of columns to each of them with default value (NULL actually). Can I do this without reloading the data? Otherwise it will be huge amount of data and time to do this

Add a column to existing QVD

rwunderlich — Tue, 02 Aug 2011 00:10:21 GMT

I don't think you can do this reliably without reading and writing each QVD. You can do that in a filelist loop pretty easily.

Depending on how you are reading these old files, you may consider just introducing the new field as it's needed by someone consuming the old qvd. you can proably detect that the field is missing by checking something like QvdNoOfFields().

-Rob

Add a column to existing QVD

Tue, 02 Aug 2011 00:47:00 GMT

Yeah, I know this. I have such script. But to reload all of these files it can take about a week. I thought about some easier way to do this like changing a QVD file header or so.

Add a column to existing QVD

Fri, 18 Nov 2011 15:37:05 GMT

I've just had a similar situation, where in QVD files over time, fields may well get added in or removed, causing Qlikview to have different fields, for different QVD files and causing the Filelist qvd looping trick to stop working. I resolved my issues with a similar script to this:

//Markets QVD Load

SET vTablesLoaded = 0;
FOR EACH File IN FILELIST ('$(vDirectory)\Markets\*.qvd')
IF $(vTablesLoaded) = 0 THEN
Markets:
LOAD
*
FROM [$(File)] (qvd);
SET vTablesLoaded = 1;
ELSE
CONCATENATE LOAD
*
FROM [$(File)] (qvd);
ENDIF
NEXT File

I essentially added in a control variable to dictate which file is a first load into Qlikview and which is a subsequent one (in that same table). Then using an IF statement controlled the LOAD statements by setting the first one to be a standard load and the subsequent ones to be forced concatenations. This has the effect of delivering a NULL value to the column if there was no data for the period or QVD in question.

Have a play with that and see if you can get something working. For clarity, I have a Gender field added to some subsequent loads and now when I look at the front end any model which didn't have the Gender field in just greys out the Gender field, indicating null values, whilst all other models are able to use the field successfully.

Cheers,

Paul Dutton