Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
Not applicable

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

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:

ProjectIncome AIncome BIncome CIncome D
14331
25672
37121
42582

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 TypeAmountOffsetMultiplier
ASum(Income A) = 4 + 5+ 7+ 2 = 1801
BSum(Income B) = 3 +6 +1+  5 = 151-1
CSum(Income C) = 3 + 7 + 2 + 8 = 2011
DSum(Income D) = 1 + 2 + 1 + 2 = 61-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

1 Solution

Accepted Solutions
Highlighted

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

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;


Capture.PNG

View solution in original post

5 Replies
Highlighted
senarath
Contributor III

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

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

Highlighted
Chanty4u
Esteemed Contributor III

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

Highlighted

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

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;


Capture.PNG

View solution in original post

Highlighted
Not applicable

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

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

Highlighted
rupesh632
Contributor

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

s than a minute ago

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

  

Stringjobtype1jobtype2jobtype3Value

1                coil1              coil2         coil3              40

After transpose

 

String1
jobtype1coil 1
jobtype2coil2
jobtype3coil 3
Value40

and i can use job type as column