combine two tables ones live one archive

[#1]

the data is identical just need to combine the too?

[#2 · Accepted solution]

Table:

load field1,

     field2,

fildn,

'Biling' as Type

from ...;

concatenate(Table)

load field1,

     field2,

fildn,

'Biling archive' as Type

from ...;

[#3]

I believe it worked but I am picking up 21,000 more records?

[stevedark]

Hi Robert,

If both tables are truly the same (including case of field names etc.) they will auto-concatenate.  This functionality, whilst useful, can be a bit confusing if you are not expecting it.  To force auto-concatenation you can add fields to make the table the same - or you can just force concatenation, as in Rodrigo's example.  It's always worth adding an identifier, as he has also.

If you are loading from QVD though, be aware that a forced concatenation will break optimised load.  It therefore becomes essential to load the largest (archive?) table first - so that is quick and the other table can load unoptimised.

For more info on optimised loads please see this post:

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Steve

[#5]

the number of records will be Table 1 plus the table 2

[stevedark]

Are there duplicates between the two tables?

You may need to do a WHERE NOT EXISTS, to enable this you will need to have a unique ID to use in the not exists.  If the field is not truly unique rows could be lost that you don't mean to.

The syntax is simply:

LOAD
  fields

FROM table

WHERE NOT EXISTS(IDField);

Hope that helps,

Steve

[#7]

I had one extra field in the archive table.

[#8]

Now I get a lot of SYN KEYS

[#9]

OK I think I got it.....  I copied the fields from the Billing table and pasted into the Billing archive and reloaded WITHOUT the  concatenate and well guess what IT WORKED LOL....Thank you!

[stevedark]

Excellent!  If you wanted the extra field from the billing archive (which you have now removed) just add the following to the billing load:

,

null()  as FieldName

FROM Billing ....

;

Where FieldName is the field that is available in the archive but not in the current table.  This will allow the auto concatenate (and no synthetic keys) without losing a field.

Steve