Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 excel files with the same columns which I want to load as one merged "table". Just to emphasis - it's not two files I want to connect.
But when I load them - I get 2 "data sets" - instead of seeing each field just once - I see it twice - once from one file and once from the other.
How do I do it? Does it require programming?
Thanks,
Nava
If the two Excel-files have exactly the same columns they should appear as one single table.
However if there are any type of differences between the number of columns and the names of the columns then they will appear as two or even three tables (due to synthetic key generation).
You can force them to be concatenated though by doing
TABLE:
LOAD
*
FROM
FirstExcelFile.xlsx (ooxml.......);
CONCATENATE LOAD
*
FROM
SecondExcelFile.xlsx (ooxml.......);
Afterwards you will see one table and all of the columns - also those that weren't common to both.
Please post the script you are using
The field names must be the same in both the tables, so they are concatenated in the same field list otherwise you will see separate columns
If the fields names of both the files are same then auto concatenation will work.
If field names are not same, then rename the fields. If no. of fields are different in both files then use Concatenate keyword between load statement.
You can refer below link for more information:
http://www.learnallbi.com/concatenate-and-noconcatenate-in-qlikview-part-1/
Concatenate and NoConcatenate in QlikView – Part 2 – Learn QlikView
Hi All,
Thanks for your help - but although it's the same fields it's not working.
to make sure it's the same name - I copied the header line from one file to the other, and now I can load both together, but somehow the "prepare data" that creates all the data attributes doesn't work when loading the second files.
This is the script I'm getting (I can't print screen for some reason):
[Data$_af431949-b433-d50f-5ae9-6daead56]:
LOAD [Call ID],
[End User],
[Call Date],
[Name],
[Category],
[Item Number],
[Description],
[Rev Level],
[Serial],
[Description1],
[Install Date],
[Warranty Code],
[Status],
[Warranty Expiration],
[Type],
[Problem],
[Cause],
[Comments],
[Resolution]
FROM [lib://AttachedFiles/call hist.xls]
(biff, embedded labels, table is Data$);
[Data$-1]:
LOAD [Call ID],
[End User],
[Call Date],
[Name],
[Category],
[Item Number],
[Description],
[Rev Level],
[Serial],
[Description1],
[Install Date],
[Warranty Code],
[Status],
[Warranty Expiration],
[Type],
[Problem],
[Cause],
[Comments],
[Resolution]
FROM [lib://AttachedFiles/call.xls]
(biff, embedded labels, table is Data$);
RENAME TABLE [Data$_af431949-b433-d50f-5ae9-6daead56] TO [Data$];
RENAME TABLE [Data$] TO [Data$-Data$-1];
both your tables would auto concatenate because you have same fields in both the tables, you will only have one big table to work with
read these threads
auto concatenate & force concatenate pls explain?
Thanks.
It looks like they do concatenate - but the calendar part (when the system creates quarter, YearQtr etc dimensions is deleted after the concatenating.
I will try to add it manually.