How to convert rows to columns

[Mattia]

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

[hic · Accepted solution]

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

[Mattia]

Thank you Henric!!!

It works perfectly.

Mattia

[Mattia]

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

[hic · Accepted solution]

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
;

[Mattia]

Thank you so much!!!

Mattia