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

Loading Excel spreadsheet with four worksheets

Hi,

I have one spreadsheet with four work sheets, each worksheet contains unique information, but the structure (columns/names) is 95% same, only a few filed were added to two of the worksheets.

Use quick load, selected all worksheets, load the data.

Created a new sheet, added a Bar Chart, added dimension and measure, applied filters to a specific value which occurs only in one of the worksheets 5 times, however the value presented in the bar chart 15 (3 times what should be)

Open Data Modeler; find out that there are 3 tables. One of them contains the merger of all three. That is why the value in the bar chart shows 3 times what it should be.

How can I eliminate the triple count?

Thank you - Mauricio

11 Replies
Gysbert_Wassenaar

Try forcing concatenation of the data so everything ends up in one table:

Data:

LOAD * FROM  [myexcelfile.xls] (biff, embedded labels, table is Sheet1$);

Concatenate(Data)

LOAD * FROM  [myexcelfile.xls] (biff, embedded labels, table is Sheet2$);


Concatenate(Data)

LOAD * FROM  [myexcelfile.xls] (biff, embedded labels, table is Sheet3$);


Concatenate(Data)

LOAD * FROM  [myexcelfile.xls] (biff, embedded labels, table is Sheet4$);


talk is cheap, supply exceeds demand
Not applicable
Author

Gysbert,

Thank you for your reply. I am new to Qlik and not sure how to apply your solution.

Can you please include step by step instructions?

Thank you,

Mauricio Mulé

AIG

Business Architecture Project Manager

Global Vendor and Business Partner Services - Supply Chain<https://live.contact.aig.net/sites/contact/pc/gcos/cuop/Pages/GVBC.aspx> | Property Casualty

32 Old Slip – 19th Fl., New York, New York 10005 | Tel +1 646.857.1686 |

SilvanoMauricio.Mule@aig.com<mailto:SilvanoMauricio.Mule@aig.com> | www.aig.com<http://www.aig.com/>

Gysbert_Wassenaar

You're not talking about Qlikview, but Qlik Sense, aren't you? Ok, first have a look at this video:

Next read the help page on Concatenate:

http://help.qlik.com/sense/en-us/online/#../Subsystems/Hub/Content/LoadData/ConcatenateTables.htm?Hi...


talk is cheap, supply exceeds demand
Not applicable
Author

Yes, I am using Qlik Sense.

Thank you for the video, it works fine now.

Mauricio Mulé

AIG

Business Architecture Project Manager

Global Vendor and Business Partner Services - Supply Chain<https://live.contact.aig.net/sites/contact/pc/gcos/cuop/Pages/GVBC.aspx> | Property Casualty

32 Old Slip – 19th Fl., New York, New York 10005 | Tel +1 646.857.1686 |

SilvanoMauricio.Mule@aig.com<mailto:SilvanoMauricio.Mule@aig.com> | www.aig.com<http://www.aig.com/>

Not applicable
Author

Hi Gysbert,

I have one more question, the four worksheet have common fields (Excel Columns)

One specific filed, say Test1, in one spreadsheet have three different entries, High, Medium, Low. However the other three worksheets (field Test1) have additional entries High1, High 2 and High 3 (total 6 entries)

When I add a Filter Pane to the Sheet, it only shows High, Medium and Low, the other entries (High1, High2 and High 3) do not show, hence I can’t select them as filters

How can I display all (6) entries in the Filter Pane?

Thank you,

Mauricio Mulé

AIG

Business Architecture Project Manager

Global Vendor and Business Partner Services - Supply Chain<https://live.contact.aig.net/sites/contact/pc/gcos/cuop/Pages/GVBC.aspx> | Property Casualty

32 Old Slip – 19th Fl., New York, New York 10005 | Tel +1 646.857.1686 |

SilvanoMauricio.Mule@aig.com<mailto:SilvanoMauricio.Mule@aig.com> | www.aig.com<http://www.aig.com/>

Gysbert_Wassenaar

Check your source data and make sure the excel sheets have the exact same case sensitive field names. TEST1, test1 and Test1 are three different fields to Qlik Sense.


talk is cheap, supply exceeds demand
Not applicable
Author

Yes, the filed names are identical, the four worksheet originated from the same template.

What other reason may cause it?

Thank you,

Mauricio Mulé

AIG

Business Architecture Project Manager

Global Vendor and Business Partner Services - Supply Chain<https://live.contact.aig.net/sites/contact/pc/gcos/cuop/Pages/GVBC.aspx> | Property Casualty

32 Old Slip – 19th Fl., New York, New York 10005 | Tel +1 646.857.1686 |

SilvanoMauricio.Mule@aig.com<mailto:SilvanoMauricio.Mule@aig.com> | www.aig.com<http://www.aig.com/>

Gysbert_Wassenaar

No idea. I'll have to look at the qlik sense app and the source files to determine that.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert,

I followed the video, step-by-step.

Now I can see the data aggregation from each worksheet. However, the charts show triple count.

Column Test1 in worksheet 1, has 3 records and the other the worksheet has no records. The count should be 3. However, it shows 9.

Noticed Data model viewer shows three tables SSyn 7 Table (main table) and two more tables with the name of two of the worksheets.

How may I eliminate the triple count?

Thank you,

Mauricio Mulé

AIG

Business Architecture Project Manager

Global Vendor and Business Partner Services - Supply Chain<https://live.contact.aig.net/sites/contact/pc/gcos/cuop/Pages/GVBC.aspx> | Property Casualty

32 Old Slip – 19th Fl., New York, New York 10005 | Tel +1 646.857.1686 |

SilvanoMauricio.Mule@aig.com<mailto:SilvanoMauricio.Mule@aig.com> | www.aig.com<http://www.aig.com/>