Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Perfect! many thanks