Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

combine two excel files into one qlikview file

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.

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: combine two excel files into one qlikview file

Sorry forgot to attach. Find the attachment!!!

11 Replies
NickHoff
Valued Contributor

Re: combine two excel files into one qlikview file

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

Re: combine two excel files into one qlikview file

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

MVP
MVP

Re: combine two excel files into one qlikview file

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?

1.png

Not applicable

Re: combine two excel files into one qlikview file

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

Not applicable

Re: combine two excel files into one qlikview file

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

Re: combine two excel files into one qlikview file

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?

Capture.PNG

vishsaggi
Esteemed Contributor III

Re: combine two excel files into one qlikview file

Sorry forgot to attach. Find the attachment!!!

Not applicable

Re: combine two excel files into one qlikview file

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

Re: combine two excel files into one qlikview file

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.

Community Browser