topic Re: convert field values into columns Straight.T in QlikView

convert field values into columns Straight.T

CreoLTD112 — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

Im Fairly new to Cliq and coding, just wanted to find out..

I have created a straight table containing the following fields

Modified by|Client|Time|Category|Value etc..

values running below the column Category are e.g: 1,2,3,4

I would like to know how can i these fields from running vertically to horizontally as individual fields e.g:

Modified by|Client|Time|1|2|3|

any advise or assistance would be highly appreciated, and i do not want to do this with a pivot table, preferably changes made within expressions? just not sure how

Thanks

Re: convert field values into columns Straight.T

sunny_talwar — Thu, 29 Nov 2018 16:00:19 GMT

May be use one expression for each Category..... not sure if you only have 4 categories, but if you have more than 4 and they can change over time, I would suggest you to go with the pivot table.

Re: convert field values into columns Straight.T

jwjackso — Thu, 29 Nov 2018 16:07:03 GMT

check the generic table example at https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/DataSource/generic-databases.htm

Re: convert field values into columns Straight.T

CreoLTD112 — Thu, 29 Nov 2018 16:08:43 GMT

Thanks,however really avoiding the pivot route..its 5 categories which will remain fixed , can you clarify abit more on how this would be accomplished

Re: convert field values into columns Straight.T

sunny_talwar — Thu, 29 Nov 2018 16:15:58 GMT

Sure.... assuming your expression right now is

Sum(Value)

now you can create 5 expressions like this

Sum({<Category = {'1'}>} Value)
Sum({<Category = {'2'}>} Value)
Sum({<Category = {'3'}>} Value)
Sum({<Category = {'4'}>} Value)
Sum({<Category = {'5'}>} Value)

Re: convert field values into columns Straight.T

CreoLTD112 — Thu, 29 Nov 2018 17:06:23 GMT

Thanks still dosent seem to be working though, the original data falls part of a column eg: category| fields-from excel Fields under category are required, maybe this could be the issue I am able to extract the values in a straight table (values being 1,2,3,4,5) however need it to display horizontally.

Re: convert field values into columns Straight.T

sunny_talwar — Thu, 29 Nov 2018 17:11:27 GMT

I am not sure I follow... do you have an image you can show of what you had before and an image of what happened after you use the above expression?

Re: convert field values into columns Straight.T

CreoLTD112 — Tue, 04 Dec 2018 10:47:15 GMT

Hi apologies for the really delayed response, it did work however it just duplicates the exact values

Re: convert field values into columns Straight.T

Colin-Albert — Tue, 04 Dec 2018 10:54:20 GMT

You can transpose the rows and columns by checking the Horizontal option on the Presentation tab of the Chart Properties.

Re: convert field values into columns Straight.T

sunny_talwar — Tue, 04 Dec 2018 11:56:53 GMT

Can you share an image of what you have and explain (in reference to your image) what you are looking to get?

Re: convert field values into columns Straight.T

CreoLTD112 — Tue, 04 Dec 2018 11:59:55 GMT

Hi, turns out i incorrectly named my naming convention e.g: Category should of been category- lower case c, which worked perfectly..thanks guys

Re: convert field values into columns Straight.T

CreoLTD112 — Tue, 04 Dec 2018 12:00:23 GMT

Hi, turns out i incorrectly named my naming convention e.g Category should of been category- lower case c, which worked perfectly..thanks guys