Switching column-data into rows

[#1]

Dear all

I have following source table where an undefined number of identical data in col. A could exist and other data in col. B (current example shows 3x '1' and twice '2' in col. A)

I tried to get following target table upon loading the file, but neither succeeded myself nor found any solution.

Does anybody have an idea?

Thanks a lot for your help.

Paul

[alexandros17]

I have the following idea:
suppose that the table is myTable with columns number and data

T1:
load distinct number from myTable

for i = 1 to nrows() -- test this condition
let value = peek('number',$(i),T1)

left join

load
number,
data
from myTable
where number = $(value);

next

n times as the number of rows in T1

[#3]

Try this, it will get you ptretty close....

tmpTable:

LOAD * INLINE [

    Number, Data

    1, 1

    1, 2

    2, 3

    3, 4

    2, 4

    4, 5

    5, 6

    6, 7

    7, 8

    8, 9

    1, 10

    9, 12

];

//

//Count_Table:

//Load Number,

//Count (Number) as count

//resident tmpTable group by Number;



let cols = 5;



Table:

Noconcatenate Load *,

Number &'-' & Data as Key

resident tmpTable order by Number, Data;



drop table tmpTable;



FINAL_TABLE:

Load if (Number<>Previous(Number), Number) as New_Num,

if (Number<>Previous(Number), Data) as 1,

if (Number<>Previous(Number), Key) as New_Key

resident Table;



left join (Table) Load

New_Key as Key,

'1' as read

Resident FINAL_TABLE;





New_Table:

NoConcatenate Load Number,

Data,

Key

Resident Table where IsNull(read)=-1;



drop table Table;

Rename Table New_Table to Table;



//drop table Count_Table;



for i=2 to $(cols)





Concatenate (FINAL_TABLE) Load

if (Number<>Previous(Number), Number) as New_Num,

if (Number<>Previous(Number), Data) as $(i),

if (Number<>Previous(Number), Key) as New_Key

resident Table;





left join (Table) Load

New_Key as Key,

'1' as read

Resident FINAL_TABLE;



New_Table:

NoConcatenate Load Number,

Data,

Key

Resident Table where IsNull(read)=-1;



drop table Table;

Rename Table New_Table to Table;





next



drop table Table;

drop field New_Key;

[#4]

Thanks Alexandros

I am afraid, but it doesn't work.

Paul

[#5]

Thanks a lot.

Yeah looks much better, but still not as my goal.

I'll do further "exploration" based on your suggestion.

Kind regards

Paul

[#6]

Thanks a lot.

Yeah looks much better, but still not as my goal.

I'll do further "exploration" based on your suggestion.

Kind regards

Paul

[Gysbert_Wassenaar · Accepted solution]

If you can live with the result being a pivot table then have a look at the attached qvw. The underlying data will have only three fields, but the pivot table looks like you want afaict.

[#8]

Dear Gysbert

Thanks a lot. Your code is really great and helped me to finalize my task which was to LEFT JOIN your resulting table to another Excel-file with various and different "Number".

I would like to quote the whole code here, but as I am new to the community, I don't know how to do that nor how to attach a QVW-file and could not find anything searching the community. Sorry.

Kind regards

Paul

Result after final completion of task: