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: 
ts3st1000
Contributor
Contributor

Transpose columns in the display

Hi,

In the database, I have a large table that i load in the load editor part of which looks like this:

 

CountryYearSales
Japan201010
Japan201111
Japan201212
Japan201313
Japan201414
Germany2010100
Germany2011110
Germany2012120
Germany2013130
Germany2014140
UK20101000
UK20111100
UK20121200
UK20131300
UK20141400

I am trying to use the data above to display the following table:

CountryTotal SalesYear 2010Year 2011Year 2012Year 2013Year 2014
Japan601011121314
Germany600100110120130140
UK600010001100120013001400

Could you please suggest a solution to this. Thanks for the help.

Regards,

Ra

18 Replies
ts3st1000
Contributor
Contributor
Author

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 ?

ts3st1000
Contributor
Contributor
Author

should the data be in the same table in the database for a pivot table to work... i guess not ?

Ivan_Bozov
Luminary
Luminary

As Andy suggests, it appears that your data is wrongly linked. How have you connected the tables?

vizmind.eu
ogster1974
Partner - Master II
Partner - Master II

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.

ts3st1000
Contributor
Contributor
Author

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

ogster1974
Partner - Master II
Partner - Master II

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.

ts3st1000
Contributor
Contributor
Author

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?

ts3st1000
Contributor
Contributor
Author

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 !!!

  

CountryYear 2010Year 2011Year 2012Year 2013Year 2014
Japanif (Year = 2010, sales)if (Year = 2011, sales)if (Year = 2012, sales)if (Year = 2013, sales)if (Year = 2014, sales)
Germanyif (Year = 2010, sales)if (Year = 2011, sales)if (Year = 2012, sales)if (Year = 2013, sales)if (Year = 2014, sales)
UKif (Year = 2010, sales)if (Year = 2011, sales)if (Year = 2012, sales)if (Year = 2013, sales)if (Year = 2014, sales)
ogster1974
Partner - Master II
Partner - Master II

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.