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

[#1]

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,

JC

[senarath]

Hi

You could use the Crosstable function for your requirement.

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

thanks

[Chanty4u]

How to Transform Rows to Columns and Columns to Rows,

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

chk dis

[sunny_talwar · Accepted solution]

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;

[#5]

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

[Anonymous]

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