topic Re: How to convert rows to columns in QlikView

How to convert rows to columns

Mattia — Wed, 13 Nov 2019 08:21:23 GMT

Hi QV users,

i need to convert rows to columns.

RAW DATA

KEY_FIELD1	KEY_FIELD2	CODE_1	QTY_1	CODE_2	QTY_2	CODE_3	QTY_3	CODE_4	QTY_4
12345678	01	8952	001
12345678	02	897	001	992	001	992	001	992	001

OUTPUT

KEY_FIELD1	CODE	QTY	PROG
12345678	8952	001	1
12345678	897	001	2
12345678	992	001	3
12345678	992	001	4
12345678	992	001	5

So, i would like to see a single row for each codes (only the codes that are not null) and every row must have a new field called PROG (with a numerical sequences).

It's important the order of single rows. First i want the codes with the value "01" in key_fields2 and after the codes with the value "02" in key_fields2.

I've read many posts with possibile solutions (e.g. crosstable, rowno(), autonumber, etc.) but i'm a newbie so i don't know what is the right function.

Attached is a sample data file.

Please, someone can bring me in the right direction?

Thanks,

Mattia

Re: How to convert rows to columns

hic — Wed, 13 Nov 2019 12:08:53 GMT

CorrectTable:
Load
RowNo() as Prog,
*
Where Len(Trim(Code))>0;
Load
KEY_FIELD1 as Key_Field1,
KEY_FIELD2 as Key_Field2,
Pick(IterNo(),CODE_1,CODE_2,CODE_3,CODE_4,CODE_5,CODE_6,CODE_7,CODE_8) as Code,
Pick(IterNo(), QTY_1, QTY_2, QTY_3, QTY_4, QTY_5, QTY_6, QTY_7, QTY_8) as Qty
Resident OriginalTable
While IterNo()<=8
Order By KEY_FIELD1, KEY_FIELD2
;

/HIC

Re: How to convert rows to columns

Mattia — Wed, 13 Nov 2019 13:29:15 GMT

Thank you Henric!!!

It works perfectly.

Mattia

Re: How to convert rows to columns

Mattia — Wed, 13 Nov 2019 13:57:37 GMT

Henric, last thing.

The values in the field "PROG" must be resetted for every key_field1.

e.g.

1) now

key field1, key field2, code, prog

00000001, 01 987 1

00000001, 02 965 2

00000002, 01 987 3

00000002, 02 976 4

2) desired

key field1, key field2, code, prog

00000001, 01 987 1

00000001, 02 965 2

00000002, 01 987 1

00000002, 02 976 2

How can i modify your expression?

Thanks,

Mattia

Re: How to convert rows to columns

hic — Wed, 13 Nov 2019 14:16:19 GMT

CorrectTable:
Load
If(Key_Field1=Peek(Key_Field1),Peek(Prog)+1,1) as Prog,
// RowNo() as Prog,
*
Where Len(Trim(Code))>0;
Load
KEY_FIELD1 as Key_Field1,
KEY_FIELD2 as Key_Field2,
Pick(IterNo(),CODE_1,CODE_2,CODE_3,CODE_4,CODE_5,CODE_6,CODE_7,CODE_8) as Code,
Pick(IterNo(), QTY_1, QTY_2, QTY_3, QTY_4, QTY_5, QTY_6, QTY_7, QTY_8) as Qty
Resident OriginalTable
While IterNo()<=8
Order By KEY_FIELD1, KEY_FIELD2
;

Re: How to convert rows to columns

Mattia — Wed, 13 Nov 2019 14:23:06 GMT

Thank you so much!!!

Mattia