Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Add a field to a QVD file

Hello !

We have a infra-structure here where our Unisys mainframe databases are dumped onto QVD files. So, we have a kind of a QVD database.

I want to pick some of this QVD files and add a column to it , keeping all of the other columns intact.

So say that the QVD has fields A,B and C. I want to add a D field , which is say a formula of A + B.

What would be the best way to do it ?

Thanks !

PS : have not found how to upload a file here but, if you want a kit to start rapidly your test, get one from here :

http://www.des.online.unimep.br/au/pub/kit116.zip

Tags (2)
1 Solution

Accepted Solutions
blaise
Contributor III

Add a field to a QVD file

Csv:

LOAD

A,

B,

C,

D

from csv.qvd;

join (CSV) LOAD

A,

A*B as E

resident CSV;

12 Replies
Not applicable

Add a field to a QVD file

I am not quite sure I understand your problem.

When you say that that the databases are dumped onto QVD do you mean you have a QVW that does a 'LOAD' and then 'STORE' ? If so why not create the new field in that. Otherwise, what is the mechanism that dumps the data into QVDs - why not create the new field through the SQL statement or whatever?

Regards,

Gordon

Not applicable

Add a field to a QVD file

That's it : I have a empty QVW (no interface) that reads a CSV file , generated from the mainframe, and stores it into a QVD.

As a matter of fact it works like this : a Cobol program, running on a Unisys mainframe, extracts table data and generates a CSV. It also generates automatically the script that will transform this into a QVD. When both are ready, it triggers QV.EXE outside of it (outside of the mainframe) to run upon them and generate the QVD file.

We did this all. It's working pretty nice.

So, when we build our final applications, we only read QVDs. Way faster. And it's very organized/automated.

Get a sample of a script like this from here, if you will : http://www.des.online.unimep.br/au/pub/FCMBI.QVS

So, what's the problem ? The problem is that many, many times a QVD has been used amongst several applications (QVWs). And, on all of them we're repeating a complex formula to calculate derivated fields.

So, I wanna generate this QVD with this calculated field already. I could do that on the mainframe. That is, I could do a pseudo-field only to carry on that new value, calculated. But I'd be wasting mainframe time to do that. I only would do this (and I already do it in fact) if the derivation formula can only be evaluated ON THE MAINFRAME.

On other situations, I can use low cost CPUs to calculate the fields.

After all, I want simply do something like this :

Load all of the fields from a QVD, add some more fields to it (columns) and then, store it again.

Easy ?

blaise
Contributor III

Add a field to a QVD file

Csv:

LOAD

A,

B,

C,

D

from csv.qvd;

join (CSV) LOAD

A,

A*B as E

resident CSV;

Not applicable

Add a field to a QVD file

If you dont want to change the existing process, you can load a QVD with derived fields and then store it back to the original name:

[in]:

LOAD A,

B,

C,

D,

B * C as E

FROM test.qvd (qvd);

store [in] into test.qvd;

If you want to automate this perhaps you could initiate a batch file containing 2 calls to qv.exe (using /r) to create the 'original' QVD then another to recreate it in the 'extended' format.

Regards,

Gordon



Not applicable

Add a field to a QVD file

Sounds great !

I did it like this :

JOIN (CSV) LOAD

*

A*B AS E

RESIDENT CSV;

Very nice.

I should understand better the JOIN statement. It looks very flexible.

Thanks !

Not applicable

Add a field to a QVD file

Gordon.savage :

I prefer this method :

[in]:

LOAD A,

B,

C,

D

FROM test.qvd (qvd);

JOIN [in] LOAD

*

B*C AS E

RESIDENT [in];

store [in] into test.qvd;


Add a field to a QVD file

If you use the JOIN method, make sure to use the DISTINCT keyword and load enough key fields to create uniqueness -- otherwise you may generate additional rows. For a calculated field, it should be enough to list only the fields used in the calc.

join (Csv) load DISTINCT A, B, A * B as E resident Csv

-Rob

Not applicable

Add a field to a QVD file

Well, every table of mine has a field called RECNO, which is the result of the RECNO function. This is unique by nature.

Can't I use simply the "*" (star) syntax ?

Like this :

join (Csv) load DISTINCT * , A * B as E resident Csv

IOW:

load *anything* and add a new field E with the value A*B.

I just want to create a new column. CAN NOT, CREATE NEW ROWS. NEVER. NOT AT ALL.

Thanks !

Add a field to a QVD file

Yes, you can use the "*" which is the way I always do it. Should have typed my original answer that way.

-Rob

Community Browser