topic How to convert columns to rows in the data load editor? in App Development

How to convert columns to rows in the data load editor?

Mon, 18 Jan 2016 08:41:56 GMT

Hello everyone,

I'm currently loading a table from Excel with the following format (each row corresponds to a project, say project 1, 2, 3 and 4:

Project	Income A	Income B	Income C	Income D
1	4	3	3	1
2	5	6	7	2
3	7	1	2	1
4	2	5	8	2

To load it, I use:

Taula:

Load

Project,

"Income A",

"Income B",

"Income C",

"Income D",

From [lib://data.xlsx]

(ooxml, embedded labels, table is [Project Revenues]);

So, until here I haven't had any issues, it's pretty basic stuff. The thing is that what I need is a table with the following format (to make a waterfall chart), getting data from the previous table (using resident):

Income Type	Amount	Offset	Multiplier
A	Sum(Income A) = 4 + 5+ 7+ 2 = 18	0	1
B	Sum(Income B) = 3 +6 +1+ 5 = 15	1	-1
C	Sum(Income C) = 3 + 7 + 2 + 8 = 20	1	1
D	Sum(Income D) = 1 + 2 + 1 + 2 = 6	1	-1

My best idea so far has been to load an inline table as such:

LOAD * INLINE [

Income Type, Offset, Multiplier

A, 0, 1

B, 1, -1

C, 1, 1

D, 1, -1

];

And then trying to concatenate a column containing the sums of each type of income, but have been unsuccessfull so far. This is what I've come up with, even though it doesn't do what I want it to do:

aux:

Load

SUM("A")

SUM("B")

Sum("C")

Sum("D")

RESIDENT Taula;

And trying to concatenate this to the other inline table.

Can anyone give me a hand?

Thank you very much,

Re: How to convert columns to rows in the data load editor?

senarath — Mon, 18 Jan 2016 09:38:46 GMT

You could use the Crosstable function for your requirement.

https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable?et=blogs.comment.created#comment-42030

thanks

Re: How to convert columns to rows in the data load editor?

Chanty4u — Mon, 18 Jan 2016 09:42:02 GMT

How to Transform Rows to Columns and Columns to Rows,

https://community.qlik.com/thread/200397?sr=inbox&ru=212483

chk dis

Re: How to convert columns to rows in the data load editor?

sunny_talwar — Mon, 18 Jan 2016 10:38:47 GMT

Try this:

Table:

CrossTable([Income Type], Amount)

LOAD

Project,

"Income A",

"Income B",

"Income C",

"Income D"

FROM [lib://Qlik]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD [Income Type],

Sum(Amount) as Amount

Group By [Income Type];

LOAD Replace([Income Type], 'Income ', '') as [Income Type],

Amount

Resident Table;

Left Join (FinalTable)

LOAD * INLINE [

Income Type, Offset, Multiplier

A, 0, 1

B, 1, -1

C, 1, 1

D, 1, -1

];

DROP Table Table;

Re: How to convert columns to rows in the data load editor?

Mon, 18 Jan 2016 11:50:58 GMT

This is exactly what I needed, thank you! Got it working in no time.

Re: How to convert columns to rows in the data load editor?

Anonymous — Fri, 22 Dec 2017 15:48:53 GMT

s than a minute ago

i want to use job type in x axis to get vlaues,

String

jobtype1

jobtype2

jobtype3

Value

1 coil1 coil2 coil3 40

After transpose

String	1
jobtype1	coil 1
jobtype2	coil2
jobtype3	coil 3
Value	40

and i can use job type as column