combine two excel files into one qlikview file

[#1]

Hello,

Building metric from two excel files with different data values. Is it possible? From my research I bumped into many tutorials on how to merge multiple sheets/files with same header rows. In my case, I would like to combine the two files

I attach a sample file with nonsensical data value. The end goal of this spreadsheet is on tab "Goal". Please note that the sample file is combined into same file for convenience purpose, in reality they're two different files.

Please let me know if anyone has some thoughts on this. Thanks! I need some insights on how to make this happen, or if it's achievable at all.

[NickHoff]

Have you attempted to do a LEFT JOIN?

I'm not sure what your calculations are, but if you provide them we can get the script to work correctly.  Currently it's just using an inline table with your current data.  See the attached.

Also, we need to create a key value to join the two tables correctly.  I'm thinking Name isn't enough on it's own, perhaps.  Perhaps Name-Age as a Key?

[vishsaggi]

What is your expected output values can you populated some as an example?

You can try this and check :

Ex1:

LOAD Name,

     age,

     bday,

     salary,

     yahoo,

     manual,

     easter,

     lovely

FROM

[..\Desktop\Rosanna sample.xlsx]

(ooxml, embedded labels, table is raw);

JOIN (Ex1)

Ex2:

LOAD Name,

     age,

     bday,

     salary,

     time,

     limit,

     [Hrs./month],

     Measurement

FROM

[..\Desktop\Rosanna sample.xlsx]

(ooxml, embedded labels, table is calculation);

[maxgro]

In goal tab you asked for "all unique names from tab1 and tab2" and for "age"

What's the result you want for Name b?

1 row? Which Age?

3 rows?

[#5]

I think they can be sum of B's age.

[#6]

expected output could be sum of salary, sum of time, sum of hrs./month, sum of lovely, sum of easter, based on names from the two tabs "Raw" and calculation"

[vishsaggi]

Load the load statements i have sent in my earlier post and follow ->

Add Straight table chart

Dim: Name

Expr: Sum(age), Sum(time) ....... Sum(Fieldnames)

Like below?

[vishsaggi · Accepted solution]

Sorry forgot to attach. Find the attachment!!!

[#9]

Hi Vish,

I tried this script:

Ex1:

LOAD Name,

    age,

    bday,

    salary,

    yahoo,

    manual,

    easter,

    lovely

FROM

[..\Desktop\Rosanna sample.xlsx]

(ooxml, embedded labels, table is raw);

JOIN (Ex1)

Ex2:

LOAD Name,

    age,

    bday,

    salary,

    time,

    limit,

    [Hrs./month],

    Measurement

FROM

[..\Desktop\Rosanna sample.xlsx]

(ooxml, embedded labels, table is calculation);

However, join(ex1)

Ex2: is showing error, says can't find field....

Do I input Join(ex1)

ex2 as it is? or should I replace some wordings with my actual file name/sheetab?

Also, is it possible to change columns into row headers instead of column headers? as attached.

Thanks for your help.

[vishsaggi]

Ok. I believe the field name coming from one of the excel tabs is not correct. Can you cross check and modify the fieldname in Ex2 Table to what you have in the excel. Or send me the correct excel sheet i will populate again and modify and send you back.