How to convert Rows into Columns

[Anonymous]

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

[Frank_Hartmann]

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

[sunny_talwar]

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;

[ravikumar_iyana]

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

[eduardo_dimperio]

Hi,

Why you dont try to use Crosstable?

TMP:

Crosstable (SERIAL_NUMBER, PROBLEM_DETAIL)

LOAD

*

FROM YourQVD

[florentina_doga]

or generic load?

[rwunderlich]

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

[effinty2112]

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

[Anonymous]

Hi,

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

Kind regards,

Carlos

[sunny_talwar]

You can use Concat() function

LOAD [Serial Number],

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

Resident ...

Group By [Serial Number];