Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
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
MVP
MVP

Re: How to load two files with different content

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
Esteemed Contributor II

Re: How to load two files with different content

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

Highlighted
payalgosar
Contributor II

Re: How to load two files with different content

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

Re: How to load two files with different content

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
Esteemed Contributor II

Re: How to load two files with different content

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

vinieme12
Esteemed Contributor II

Re: How to load two files with different content

Not applicable

Re: How to load two files with different content

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.