Multiple QVD into 1 table

[#1]

Hi

I have a qvd for each day. I load data in a for loop over the filelist.

This works fine if everything is unqualified. If I try to qualify my key values it creates table names including a number for each file, so I end up with multiple tables instead of 1.

Can I circumvent this splitting?

Thanks

Jürg

[#2]

Hi Jürg,

not quite sure what you are trying to achieve here. If you want to have one table from all the qvd's, then the field names have to be the same for each load. If you want to have different field names, you cannot have one table. Or can you explain in a bit more detail?

regards,

Hellmar

[markmccoid]

All the field names must be the same for it to create a single table unless you are using the CONCATENATE keyword.

If you want one table with a different name for each Key value, then use the CONCATENATE keyword .

Mark

[#4]

What it sounds like you are doing is loading different versions of the same qvd file into one table. Same fields but data for different days. Because your doing this your loading multiple qvds, each time your loading a qvd its causing you to create a new table. Since the field names are the same its creating $syn keys which create additional tables.

What you will want to do is concatenate the tables together. For example.

table1:

load

field1,

field2

from qvd1;

concatenate (table1)

load

field1,

field2

from qvd2;

[#5]

Hi all

Thanks for the reply. Let me be a bit more precise.

All qvd have exactly the same structure, so with the default they get concatenated.

What I wanted is to have only a few special values to be keys. So I would like to use
QUALIFY *
UNQUALIFY '%*'
to have only my key values unqualified.

When I loop over my files and my table name is e.g.
Table1:
I get for each file distinctive names like Table1_1, Table1_2 as qualifier. I need however a single table to make my controls work.

Jürg

[biester · Accepted solution]

Hi Jürg,

perhaps enclosed suggestion might help.

Rgds,
Joachim

[markmccoid]

Hi,

The reason you are getting different tables is because by qualifying the key field you are making it so that the structure is not the same each time thus QV won't concatenate them into one table.

You may be able to set up your loop so that the first time through you load your table and then subsequent times through you use the CONCATENATE keyword.

Here is an example:

qualify AccountNumber;
set firstflag = 1;

for each File in filelist ('XLS*.xls')

if firstflag then
 set firstflag = 0;
 LOAD [Sales Rep],
 AccountNumber,
 Period,
 GoalAmount
 FROM [$(File)] (biff, embedded labels, table is Sheet1$);
ELSE
 CONCATENATE
 LOAD [Sales Rep],
 AccountNumber,
 Period,
 GoalAmount
 FROM [$(File)] (biff, embedded labels, table is Sheet1$);
End if

next File

[#8]

Hi Joachim

Your method looked good, does not add a lot of code and does exactly what I wanted.

There seems to be a million ways to make a load statement, unfortunately many times they do not what I want or they do not work at all ...

Jürg

[biester]

Glad it helped. You know: TIMTOWTDI: there is more than one way to do it 😉

Rgds,
Joachim