topic Re: Adding new columns to QVD Generator without impacting old QVDS in QlikView

Adding new columns to QVD Generator without impacting old QVDS

nachiket_shinde — Wed, 25 Nov 2020 16:16:04 GMT

I have a generator which generates QVDS on daily basis with date appended to filename

xyz_20171001

xyz_20171002

xyz_20171003 and so on.

Stored at same folder

Then I have a DM where in I am calling each QVD, performing some transformations.And then creating 1 QVD out of it.

Then in my application I am using this newly created QVD.

Question:

I want to show 3 new columns in Application. So if I add them from today then DM will fail saying columns are not available in QVDs created previously.

I dont want to have those columns in old Qvds but new Qvds should have them.

Please suggest.

Please let me know if I am not clear.

Re: Adding new columns to QVD Generator without impacting old QVDS

Clever_Anjos — Mon, 06 Nov 2017 10:13:28 GMT

You can create another qvw that

Read old QVD´s adding null() as values to new columns
Store the new QVD´s

And then backup the old ones replace with the new

Re: Adding new columns to QVD Generator without impacting old QVDS

micheledenardi — Mon, 06 Nov 2017 10:14:03 GMT

You can load into your DM the old QVDs and create these new columns(fields) with null() (or 0) values (doing this your old QVDs have the original structure) and then concatenate the new QVDs where the new columns are present.

XYZ:

Load

null() as NewColumn1,

null() as NewColumn2,

null() as NewColumn3

from OldQVDxyz_2017*.qvd(qvd);

Concatenate(XYZ)

Load

from NewQVD.qvd(qvd);

Re: Adding new columns to QVD Generator without impacting old QVDS

nachiket_shinde — Mon, 06 Nov 2017 11:06:19 GMT

What if I have 250 Old QVDs

and what if in future I have to add 3 more columns to the newly generated QVDs.

eg.

Set 1:From 1st to 10th old QVDs

Set 2: From 11th to 20th new QVDs with 3 new columns

Set 3: From 21st to 30th new QVDs with 3 new columns

Now set 1 has 6 columns missing

set 2 has 3 columns missing

Re: Adding new columns to QVD Generator without impacting old QVDS

nachiket_shinde — Mon, 06 Nov 2017 11:07:53 GMT

I don't want to change structure of old QVDs if possible.So do not want to recreate them.

Re: Adding new columns to QVD Generator without impacting old QVDS

Clever_Anjos — Mon, 06 Nov 2017 12:49:02 GMT

There´s a workaround so, please make the necessary changes

QvdFieldHeader:

LOAD FieldName FROM [yourqvd.qvd] (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]) where FieldName = 'SERVICE TYPE ID'; // put your new field here

Let code=If(NoOfRows('QvdFieldHeader') > 0,'[SERVICE TYPE ID],','null() AS [SERVICE TYPE ID],'); // Change accordingly to your fields

Load

field1,

field2,

$(code)

From [yourqvd.qvd] ;

Re: Adding new columns to QVD Generator without impacting old QVDS

rwunderlich — Tue, 07 Nov 2017 02:57:11 GMT

How are you reading your QVDs in the DM? With wildcard syntax?

-Rob

Re: Adding new columns to QVD Generator without impacting old QVDS

nachiket_shinde — Tue, 07 Nov 2017 06:45:51 GMT

I am using For Loop to read each QVD.

Re: Adding new columns to QVD Generator without impacting old QVDS

kenphamvn — Tue, 07 Nov 2017 06:59:45 GMT

Using If condition into For Loop

For each filename in filelist //fine name format Xyz_YYYYMMDD
if num(right($(filename),2)) <=10  then //set 1
// add 6 columns missing
load *, null() as Col_1,null() as Col_2...null() as Col_6
resident filename;
else if num(right($(filename),2)) <=20 //set 2
//add 3 columns missing
load *, null() as Col_4,null() as Col_5...null() as Col_6
resident filename;
else
// load QDD set 3
load *
resident filename;
end if;
end if;
loop;

Regards

Re: Adding new columns to QVD Generator without impacting old QVDS

nachiket_shinde — Tue, 07 Nov 2017 08:33:11 GMT

Will it work if we do not know the number of columns that we are going to add or name of columns?

And what if we might have to add few columns in future..

Re: Adding new columns to QVD Generator without impacting old QVDS

kenphamvn — Tue, 07 Nov 2017 09:05:17 GMT

Did you try script

noconcatenate
[Templatetable]:
Load *
from xyz_YYYYMMDD.QVD
where 1=2; //any table, just get struct only


For each filename in filelist
concatenate (Templatetable)
Load *
from $(filename).qvd (qvd)
loop

Number of column in Templatetable will be max of column of table in list and auto add missing column with null value

Regards

Re: Adding new columns to QVD Generator without impacting old QVDS

rwunderlich — Tue, 07 Nov 2017 12:59:30 GMT

If you use "Load *" you don't have to worry about the number of columns. Is your load more complex than that?

-Rob

Re: Adding new columns to QVD Generator without impacting old QVDS

rwunderlich — Tue, 07 Nov 2017 13:00:22 GMT

An,

There is no need to add null() for the missing columns. That will be done by default.

-Rob

Re: Adding new columns to QVD Generator without impacting old QVDS

nachiket_shinde — Tue, 07 Nov 2017 13:14:33 GMT

LOAD *,

Transformations;

LOAD *,transformations;

LOAD transformation(Fields)

FROM xyz_date.qvd

WHERE

Condition

Currently it has 50 columns

from today new qvds will have 52 columns

how can I include these extra 2 columns in above script