topic Re: Switching column-data into rows in QlikView

Switching column-data into rows

Wed, 20 Mar 2013 12:21:37 GMT

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

Re: Switching column-data into rows

alexandros17 — Wed, 20 Mar 2013 12:44:02 GMT

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);

n times as the number of rows in T1

Re: Switching column-data into rows

Wed, 20 Mar 2013 13:50:26 GMT

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;

Re: Switching column-data into rows

Wed, 20 Mar 2013 14:02:09 GMT

Thanks Alexandros

I am afraid, but it doesn't work.

Paul

Re: Switching column-data into rows

Wed, 20 Mar 2013 14:04:42 GMT

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

Re: Switching column-data into rows

Wed, 20 Mar 2013 14:05:09 GMT

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

Re: Switching column-data into rows

Gysbert_Wassenaar — Wed, 20 Mar 2013 14:31:06 GMT

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.

Re: Switching column-data into rows

Thu, 21 Mar 2013 12:42:18 GMT

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: