Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to load two files with different content

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

7 Replies
petter
Partner - Champion III
Partner - Champion III

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.

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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

Not applicable
Author

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):

2

[Data$_af431949-b433-d50f-5ae9-6daead56]:

3

LOAD [Call ID],

4

   [End User],

5

   [Call Date],

6

   [Name],

7

   [Category],

8

   [Item Number],

9

   [Description],

10

   [Rev Level],

11

   [Serial],

12

   [Description1],

13

   [Install Date],

14

   [Warranty Code],

15

   [Status],

16

   [Warranty Expiration],

17

   [Type],

18

   [Problem],

19

   [Cause],

20

   [Comments],

21

   [Resolution]

22

 FROM [lib://AttachedFiles/call hist.xls]

23

(biff, embedded labels, table is Data$);

24

25

[Data$-1]:

26

LOAD [Call ID],

27

   [End User],

28

   [Call Date],

29

   [Name],

30

   [Category],

31

   [Item Number],

32

   [Description],

33

   [Rev Level],

34

   [Serial],

35

   [Description1],

36

   [Install Date],

37

   [Warranty Code],

38

   [Status],

39

   [Warranty Expiration],

40

   [Type],

41

   [Problem],

42

   [Cause],

43

   [Comments],

44

   [Resolution]

45

 FROM [lib://AttachedFiles/call.xls]

46

(biff, embedded labels, table is Data$);

47

50

RENAME TABLE [Data$_af431949-b433-d50f-5ae9-6daead56] TO [Data$];

51

RENAME TABLE [Data$] TO [Data$-Data$-1];

52

5
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

read these threads

Forced concatenation

auto concatenate & force concatenate pls explain?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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.