topic Re: add a column to qvd in QlikView

add a column to qvd

farolito20 — Thu, 03 Jan 2013 15:52:07 GMT

I have a qvd with this columns

id,name,age

but I add a new column, and I need to assign null() to old data without this value in this column

id,name,age,city

How to know which rows don't have a column?

Re: add a column to qvd

Thu, 03 Jan 2013 16:02:56 GMT

Dear farolito20,

if i understand your question correctly,

if you want to add a cloumn to the existing QVD file

as you said the qvd contains id,name,age.

i think you want to add City column with Null value.

then

you need to write like in the script.

Null() as CIty.

Thanks,

Mukram.

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:04:54 GMT

Yes, but this work in the first load. What about the next? if a add rows with a value in city, this will be null()

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:05:41 GMT

I need to identify wich rows don't have the city columns

Re: add a column to qvd

Anonymous — Thu, 03 Jan 2013 16:08:36 GMT

Rebuild the QVD and then save again.

TheQVD:

LOAD

ID,

Name,

Age,

If(Len(City)>0,City,Null()) AS City

FROM TheQVD.qvd (qvd);

STORE TheQVD INTO TheQVD.qvd;

This should overwrite the existing one.

Jonathan

Re: add a column to qvd

Thu, 03 Jan 2013 16:10:55 GMT

Dear farolito20,

you mean you want to find which rows have Null values?

If possible can you post your QVW file.

Thanks,

mukram.

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:11:37 GMT

Ok, but if not exist this give an error, qv not found city

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:15:41 GMT

I need which rows not have this columns. If one row not have t_seak put null, else t_seak

Re: add a column to qvd

Thu, 03 Jan 2013 16:22:38 GMT

Dear farolito20,

write the below script before loading your table.

as you said if one row not have t_seak value it will keep NULL as value in that row.

Map_Nulls:

Mapping LOAD

NULL(),'NULL'

AutoGenerate 1;

MAP * using Map_Nulls;

i hope it will help you.

else post the sample file.

Thanks,

Mukram.

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:26:40 GMT

DimOld:

LOAD

%KeyCustomer,

t_bpid,

t_nama,

t_seak,

t_ccur,

ValidFrom,

ValidTill,

MostRecent,

Autonumberhash256(t_bpid,t_nama,t_seak,t_ccur) as Checksum

FROM DimCustomers_Day0.qvd (qvd);

but in the first load, old rows not have t_seak column, so when I load this, I have an error becuase qv not found the t_seak.

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:27:11 GMT

I don't understand how to use that mapping :S

Re: add a column to qvd

Anonymous — Thu, 03 Jan 2013 16:33:24 GMT

I assume that this means that the QVD has not been loaded with City on any of it's rows and you want to provide for an exception where the structure is different to expectations.

Try something like this:

SET ERRORCODE = 0;

TheQVD:

LOAD

ID,

Name,

Age,

If(Len(City)>0,City,Null()) AS City

FROM TheQVD.qvd (qvd);

IF SCRIPTERROR=1 THEN // Field missing so load without City

TheQVD:

LOAD

ID,

Name,

Age

Null() AS City

FROM TheQVD.qvd (qvd);

ENDIF // SCRIPTERROR = 1

SET ERRORCODE = 1;

STORE TheQVD INTO TheQVD.qvd;

Hope that works for you.

Jonathan

Re: add a column to qvd

farolito20 — Thu, 03 Jan 2013 16:48:31 GMT

And where I can put

Autonumberhash256(t_bpid,t_nama,t_seak,t_ccur)

as Checksum

Re: add a column to qvd

Anonymous — Fri, 04 Jan 2013 07:52:32 GMT

Are t_bpid, t_nama, t_seak and t_ccur the fields we were discussing before, namely ID, Name, Age and City.

If so, you can add the following two lines as the last fields in the two load statements I sent before.

Load 1. Autonumberhash256(t_bpid,t_nama,t_seak,t_ccur) as Checksum

Load 2. Autonumberhash256(t_bpid,t_nama,t_seak,null()) as Checksum

As City (t_ccur) is null in the second load statement, this should be a valid syntax.

Jonathan