Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
JonnyPoole
Employee
Employee

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

View solution in original post

2 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

JonnyPoole
Employee
Employee

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