Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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$);
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/>
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:
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/>
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/>
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.
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/>
No idea. I'll have to look at the qlik sense app and the source files to determine that.
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/>