Combining multiple tables with same/similar structure

[#1]

Hello,

I am new to Qlikview, and developing a series of reports on Qlikview 10.

Our data warehouse stores different years of sales data in different tables, with some new fields added in the most recent table and the tables do not have a primary key. If I load the tables separately, will Qlikview be able to link them appropriately? E.G. Will I be able to sum sales across years stored in different tables? How will this be affected by the new fields in the latest table?

If not, how would I go about combining the tables into one. When using Access, I create a table with the structure I need, then append the data from each year. One of the big problems with this is that I quickly hit Access's file size limit, so I have to summarize the data before I can combine it.

Here's a quick mockup of what the two different tables would look like, with an excerpt of some of the fields I'd be using. The field names are identical, except the newer table has more fields I need to incorporate. What is the best way to incorporate data from all the tables (there are at least 6 I'd be pulling from). Also, the current table is named Sales_Journal. When the year turns, the data gets dumped into a new table Sales_Journal_201x. I don't imagine this would be problematic, but I'd prefer to cover my bases - would there be any problem with that?

Invoice_no	Invoice_date	Sales_type	Ship_Qty	Ext_Dlr_Net
6513563	05/12/2012	LP	9	51.26

Invoice_no	Invoice_date	Sales_type	Ship_Qty	Ext_Dlr_Net	Origin
8563526	06/17/2013	NS	2	2.36	web portal

[swuehl · Accepted solution]

Have you considered concatenating your tables into one? You can use CONCATENATE LOAD prefix to force QV to do so.

LOAD Invoice_no,

          Invoice_date,

          ...

FROM YourFirstTable;

CONCATENATE

LOAD Invoice_no,

          Invoice_date,

          ...

          Origin

FROM YourSecondTable;

[Kushal_Chawda]

if you are having same strucure you can simply load both he tables & will get auto concatenated..

Table A:

LOAD *

FROM  table1;

TableB:

LOAD *

FROM table2;

[narender123]

Hi,

You need to take the master table 1st and then concatenate with 2nd table means fact table.

Eg.

Mastertable1:

LOAD Invoice_no,

          Invoice_date,

          ------

       from mastertable1   ;

Concatenation(Mastertable1)

Facttable2:

LOAD Invoice_no,

          Invoice_date,

          ...

         from facttable2;

Thanks.

[#5]

Thank you very much, swuehl! This is what I was looking for.

I do have a followup. Can I use the Add Load function for subsequent updates to this combined dataset? Rather than pull a decade of data every time, can I load everything once with the Concatenate function, then replace that script with an Add Load on the most recent table?

[swuehl]

If I understood correctly, in priniciple, yes.

You'll need to add the ADD keyword before the last LOAD and perform first a full load, then a partial load.

You should find more details in the HELP on that.