topic Re: How to convert Rows into Columns in QlikView

How to convert Rows into Columns

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

There is one requirement in report need to show all problem details in one row against the serial number:

This is my source table:

Serial Number	Problem Detail
1234567898766	Broken
1234567898766	lock not enabled
1234567898766	no power
1234567898766	screen broken
67895423453489	no charge
67895423453489	screen Broken
98765431245678	no charge
768809215272920	No cover

The above one is my Table , Each serial number has maximum 4 problem details and they are 295 different problem details for 4 questions, I would like to get the data like below. There are millions of records. please suggest how to proceed with load script.

Finale Table Out put:

Serial Number	Problem detail 1	Problem detail 2	Problem detail 3	Problem detail 4
1234567898766	Broken	lock not enabled	no power	screen broken
67895423453489	no charge	screen Broken	-	-
98765431245678	no charge	-	-	-
768809215272920	No cover	-	-	-

Thanks for your help.

Giri

Re: How to convert Rows into Columns

Frank_Hartmann — Thu, 28 Sep 2017 13:36:33 GMT

Dimension: [Serial Number]

Expression1: subfield(concat(distinct [Problem Detail],','),',',1)

Expression2: subfield(concat(distinct [Problem Detail],','),',',2)

Expression3: subfield(concat(distinct [Problem Detail],','),',',3)

Expression4: subfield(concat(distinct [Problem Detail],','),',',4)

see attached file

Re: How to convert Rows into Columns

sunny_talwar — Thu, 28 Sep 2017 14:00:56 GMT

Here is one way to do this in script

Table:

LOAD *,

If([Serial Number] = Previous([Serial Number]), RangeSum(Peek('ProbNum'), 1), 1) as ProbNum;

LOAD * INLINE [

Serial Number, Problem Detail

1234567898766, Broken

1234567898766, lock not enabled

1234567898766, no power

1234567898766, screen broken

67895423453489, no charge

67895423453489, screen Broken

98765431245678, no charge

768809215272920, No cover

];

FinalTable:

LOAD Distinct [Serial Number]

Resident Table;

FOR i = 1 to FieldValueCount('ProbNum')

LET vField = FieldValue('ProbNum', $(i));

Left Join (FinalTable)

LOAD [Serial Number],

[Problem Detail] as [Problem Detail $(vField)]

Resident Table

Where ProbNum = $(vField);

NEXT i;

DROP Table Table;

Re: How to convert Rows into Columns

ravikumar_iyana — Thu, 28 Sep 2017 14:12:20 GMT

Hi,

Plase reffer Crosstable with cross tab.

Cross tables are table where facts are divided on the basis of dimension values.

for example we have sales for every month. then, if in our database we load sales amounts as a separated field for every month as

Product Jan Feb Mar etc

a 100 200 300

So, basically we have 1 single fact as sales amount here, and jan-dec are dimension memebers of month dimension but we are creating different fields for every dimension member and treating them as different field.

Loading data this way, makes it difficult to properly aggregrate the data over different dimensions and difficult to do analysis.

So, we convert this crosstable into a normal table.

using

crosstable (Month, Amount)

load

from a.qvd(qvd);

then we will get the same table as

Product Month Amount

a jan 100

a feb 200

a mar 300

Hope this solve your problem

Thanks

Re: How to convert Rows into Columns

eduardo_dimperio — Thu, 28 Sep 2017 14:21:11 GMT

Hi,

Why you dont try to use Crosstable?

TMP:

Crosstable (SERIAL_NUMBER, PROBLEM_DETAIL)

LOAD

FROM YourQVD

Re: How to convert Rows into Columns

florentina_doga — Thu, 28 Sep 2017 14:39:04 GMT

or generic load?

Re: How to convert Rows into Columns

rwunderlich — Thu, 28 Sep 2017 14:55:37 GMT

Problems:

Generic

LOAD [Serial Number],

'Problem Detail ' & AutoNumber(RecNo(),[Serial Number]),

[Problem Detail]

FROM

[https://community.qlik.com/thread/276195]

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

Also see: Use cases for Generic Load | Qlikview Cookbook

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to convert Rows into Columns

effinty2112 — Thu, 28 Sep 2017 15:43:53 GMT

I wish I'd read your answer before re-inventing the wheel! i'll happily delete my post, there's enough to read on this thread as it is.

Regards

Andrew

Re: How to convert Rows into Columns

Anonymous — Tue, 27 Mar 2018 05:37:02 GMT

Hi,

Is there a way to put the values for 'problem detail' in 1 column, separated by ";" ?

Kind regards,

Carlos

Re: How to convert Rows into Columns

sunny_talwar — Tue, 27 Mar 2018 08:57:29 GMT

You can use Concat() function

LOAD [Serial Number],

Concat([Problem Detail], ';') as [Problem Detail List]

Resident ...

Group By [Serial Number];