Script rows values to columns

[#1]

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!

[flipside · Accepted solution]

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

[#3]

Perfect! many thanks