Convert rows to columns

[moath]

Hi,

I would like to convert the month column to be a row. I need a way to do it in the script not in the dashboard.

Current table:

Service	Month	Price
A	January	100
A	February	100
A	March	105
A	April	110
B	January	150
B	February	160
B	March	170
B	April	180

To be as follows:

Service\Month	January	February	March	April
A	100	100	105	110
B	150	160	170	180

[Andrei_Cusnir · Accepted solution]

Hello,

In general Qlik Sense is used for Data Analysis so your exact use case scenario might not be supported 100% as you might want. However, there are a couple of workarounds that you can do to achieve the particular use case scenario.  For example you can do one of the following solutions:

1. You can Rotate table [1]
2. You can use GENERIC LOAD as stated in the post "How to convert Rows to columns" [2].

Both solutions will created multiple tables in your dataset that are linked with each other so you can use them in your visualizations or you can try concatenating them. 

Briefly trying the solution 2 I have the following outcome:

1. Script:

GENERIC LOAD Service AS Key, Month, Price AS Value INLINE [
Service, Month, Price
A, January, 100
A, February, 100
A, March, 105
A, April, 110
B, January, 150
B, February, 160
B, March, 170
B, April, 180
];
 

2. Outcome:

As you can see it has created multiple tables, but adding them on the Straight table chart, it gives the outcome that you are looking for. As I have mentioned above, this might not be the exact solution that you were looking for, but hopefully it will point you to the right path towards the resolution. 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

---

[1] https://community.qlik.com/t5/App-Development/Rotate-table/td-p/1683696

[2] https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-convert-Rows-to-columns/td-p/1701554

[eronevil]

Hello,

I think this will help you

The Generic Load - Qlik Community - 1473470

[moath]

Hi Andrei,

Thanks for your prompt response!

What if I have million records need to be converted? 

Thank you in advance.

[Andrei_Cusnir · Accepted solution]

Hello,

You can take a look at the limitations on the page "Upper limits for data tables and fields" [1]. Personally I believe that you shouldn't have any issues, as long as you don't reach the limitations mentioned on the page [1] and as long as your RAM can handle the calculations. However, you should also keep in mind that Qlik Sense is mainly used for Data Analysis, so in case you need to run more complex data preparation techniques, then you can reach to Qlik Consulting Services [2], where a professional team will help you with use case scenarios and might also demonstrate other helpful tools that Qlik offers for your specific use case scenario. 

I hope that this information is helpful!

---

[1] https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Introduction/guidelines-d...

[2] https://www.qlik.com/us/services/qlik-consulting