Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In the database, I have a large table that i load in the load editor part of which looks like this:
Country | Year | Sales |
Japan | 2010 | 10 |
Japan | 2011 | 11 |
Japan | 2012 | 12 |
Japan | 2013 | 13 |
Japan | 2014 | 14 |
Germany | 2010 | 100 |
Germany | 2011 | 110 |
Germany | 2012 | 120 |
Germany | 2013 | 130 |
Germany | 2014 | 140 |
UK | 2010 | 1000 |
UK | 2011 | 1100 |
UK | 2012 | 1200 |
UK | 2013 | 1300 |
UK | 2014 | 1400 |
I am trying to use the data above to display the following table:
Country | Total Sales | Year 2010 | Year 2011 | Year 2012 | Year 2013 | Year 2014 |
Japan | 60 | 10 | 11 | 12 | 13 | 14 |
Germany | 600 | 100 | 110 | 120 | 130 | 140 |
UK | 6000 | 1000 | 1100 | 1200 | 1300 | 1400 |
Could you please suggest a solution to this. Thanks for the help.
Regards,
Ra
yes, that is what it looks like. When i use sales against data that is in the same table, it works. Country and Sales are in two different tables, but these two tables are connected in the data model viewer , so it should work isn't it ?
should the data be in the same table in the database for a pivot table to work... i guess not ?
As Andy suggests, it appears that your data is wrongly linked. How have you connected the tables?
If country and sales are in two seperate tables there must be a field that links the two a country code for example.
Make sure the two country code fields are named the same and the Qlik data model will update correctly and your pivot table with then work.
i do have more than one link between the two tables, but the links are different from country and sales. the pivot table (country, sales) is still not working. how can this be possible ? thanks
You have to give us some more detail about how your tables are linked for us to help you further.
If you have more than one field linking the two tables I would concatenate the link fields to create one Id then name the other fields uniquely to avoid synthetic keys.
That is exactly what i have, 3 keys that are concatenated to create one link between the tables. I suppose this should mean that I could use any column from both tables as if they were one single table. Sorry I can not post data due to confidentiality.
Can you please confirm that if tables are connected using any variable in the pivot table should work?
I had another idea, which is to use a normal table (not pivot) to draw the table with a column for each year and using the expression: if(Year=201X, Sales).... but even that did not work !!!
Country | Year 2010 | Year 2011 | Year 2012 | Year 2013 | Year 2014 |
Japan | if (Year = 2010, sales) | if (Year = 2011, sales) | if (Year = 2012, sales) | if (Year = 2013, sales) | if (Year = 2014, sales) |
Germany | if (Year = 2010, sales) | if (Year = 2011, sales) | if (Year = 2012, sales) | if (Year = 2013, sales) | if (Year = 2014, sales) |
UK | if (Year = 2010, sales) | if (Year = 2011, sales) | if (Year = 2012, sales) | if (Year = 2013, sales) | if (Year = 2014, sales) |
Send over a small sample of annoimised data in the way your table is structured.
You have a data modeling issue and you are not explaining how you have structured your data. unless you shared how you have done this it is impossible for us to help you further.
Changing the chart type won't fix you data if it's not related correctly.