topic Script rows values to columns in QlikView

Script rows values to columns

Tue, 04 Nov 2014 10:11:01 GMT

Hi,

I Have two fields that I want to make from them multiple columns, how can I do that?

example below:

Data: (Date+product+type are the key)

Date Product Type Price Percent

01/01/12 A 1 10 33

01/01/12 A 1 20 40

01/01/12 A 1 30 27

01/01/12 A 2 10 60

01/01/12 A 2 20 40

Output

Date Product Type Price1 Percent1 Price2 Percent2 Price3 Percent3

01/01/12 A 1 10 33 20 40 30 27

01/01/12 A 2 10 60 20 40

Thanks!

Re: Script rows values to columns

flipside — Tue, 04 Nov 2014 10:42:57 GMT

I would create columns using dynamic script like this ...

Data:

Load *, rowno() as RowRef inline [
Date,        Product,       Type,      Price,      Percent
01/01/12,    A,             1,         10,         33
01/01/12,    A,             1,         20,         40
01/01/12,    A,             1,         30,         27
01/01/12,    A,             2,         10,         60
01/01/12,    A,             2,         20,         40
];

// identify number of cols to create
MaxCols:
Load max(MaxCols) as MaxCols;
Load count(1) as MaxCols resident Data group by Date, Product, Type;

//Dynamic script build
Let s1 = '';
Let s2 = '';

for i = 1 to peek('MaxCols')
let s1 = s1 & 'subfield(Concat(Price,' & chr(39) & ';' & chr(39) & ',RowRef),' & chr(39) & ';' & chr(39) & ',$(i)) as Price' & i & ',';
let s2 = s2 & 'subfield(Concat(Percent,' & chr(39) & ';' & chr(39) & ',RowRef),' & chr(39) & ';' & chr(39) & ',$(i)) as Percent' & i & ',';
Next i;

let s1 = s1 & '0 as a'; //dummy fields so we can handle commas
let s2 = s2 & '0 as b';

//Reload data
Output:
Load
Date,
Product,
Type,
$(s1),
$(s2)
resident Data group by Date, Product, Type;

//drop temp tables and fields
Drop Table Data;
Drop fields a,b;

flipside

Re: Script rows values to columns

Tue, 04 Nov 2014 10:57:44 GMT

Perfect! many thanks