Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Need Help in moving column to row

Is it possible to convert Row data to single column in Qlik Sense?

Current format:

BP IDYearQuarterSales
1000120141100
1000120142110
1000120143120
1000120144130
100012013390

Desired format:

BP ID2013-Q42014-Q12014-Q22014-Q32014-Q4
1000190100110120

130

BTW, desired format is possible via Pivot Table extension, but I need desired format for extract data to excel (if I extract via Pivot extension, I am still getting current format)

Thanks for your help.

1 Solution

Accepted Solutions
Employee
Employee

Re: Need Help in moving column to row

I agree this would be best served through a PIVOT table.

For a data model solution you can consider the 'Generic Load'. Which turns dimension values into fields.

Here is a sample load script using your data and screenshot is an output using a simple table object

generic LOAD

    "BP ID",

    "Year" & ' ' & Quarter as YearQuarter,

    Sales

FROM [lib://community]

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

Capture.PNG.png

Take note of the funky data model that generic loads create:

Capture.PNG.png

2 Replies
MVP
MVP

Re: Need Help in moving column to row

Hi,

Then you have to write separate expressions for each quarter

Script:

LOAD

*,

Year & '-' & 'Q' & Quarter AS YearQuarter;

LOAD

*,

QuarterStart(MakeDate(Year, Quarter*3)) AS Date

FROM DataSource;

Dimension: BP ID

Expression 1.

1. Caption - Only({<Date={'$(=QuarterStart(Max(Date), -4))'}>} YearQuarter)

Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -4))'}>} Sales)

Expression 2.

Caption - Only({<Date={'$(=QuarterStart(Max(Date), -3))'}>} YearQuarter)

Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -3))'}>} Sales)

Expression 3.

Caption - Only({<Date={'$(=QuarterStart(Max(Date), -2))'}>} YearQuarter)

Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -2))'}>} Sales)

Expression 4.

Caption - Only({<Date={'$(=QuarterStart(Max(Date), -1))'}>} YearQuarter)

Expression: Sum({<Date={'$(=QuarterStart(Max(Date), -1))'}>} Sales)

Expression 5.

Caption - Only({<Date={'$(=QuarterStart(Max(Date)))'}>} YearQuarter)

Expression: Sum({<Date={'$(=QuarterStart(Max(Date)))'}>} Sales)

Regards,

Jagan.

Employee
Employee

Re: Need Help in moving column to row

I agree this would be best served through a PIVOT table.

For a data model solution you can consider the 'Generic Load'. Which turns dimension values into fields.

Here is a sample load script using your data and screenshot is an output using a simple table object

generic LOAD

    "BP ID",

    "Year" & ' ' & Quarter as YearQuarter,

    Sales

FROM [lib://community]

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

Capture.PNG.png

Take note of the funky data model that generic loads create:

Capture.PNG.png