pivot key value pairs in load

[#1]

Hi,

I'm having a  bit of trouble working through the following ...

The data set that i am working with is structured as

unit|key|value

A|key1|val1

A|key2|val2

B|key1|val1

B|key2|val2

etc.

I would like to transform this in the load script to

Unit|Key1|Key2|...|KeyN

A|Val1|Val2|...|ValN

B|Val1|Val2...|ValN

Any suggestions would be appreciated.

[sbaldwin · Accepted solution]

Hi Take a look at the below code, (you will need to create a variable called Q with a value ' (ie a single quote ) for it to work.

Thanks

Steve

SOURCE:
LOAD unit,
     key,
     value
FROM
Book1.xls
(biff, embedded labels, table is Sheet1$);

sb:
noconcatenate load
unit,
key,
sum(value) as value,
autonumber(unit,'unit') as auto_unit,
autonumber(key,'key')  as auto_key
resident SOURCE
group by
unit,
key,
autonumber(unit,'unit') ,
autonumber(key,'key')
;

sb_lookup_1:
mapping load 
auto_unit &'_' & auto_key as link,
value
resident sb;

sb_lookup_unit:
mapping load 
autonumber(unit,'unit') as unit ,
unit as value
resident SOURCE;

sb_lookup_key:
mapping load 
autonumber(key,'key') as key ,
key as value
resident SOURCE;

// now create the table

let load_text = 'applymap(' & Q & 'sb_lookup_unit' & Q & ', recno() ) as  UNIT';
let rows = peek('auto_unit',-1,'sb');
let cols = peek('auto_key',-1,'sb');

for i=0 to cols -1
let load_text = load_text & ', applymap(' & Q & 'sb_lookup_1' & Q & ','& Q  & (i+1) & '_'& Q &' & recno()) AS ' & applymap('sb_lookup_key',i+1) ;
next i

data:
add load
$(load_text)
autogenerate(cols);

drop table SOURCE;

drop table sb;

[#3]

Hi Ukeen,

I have an idea, but that is if your data set is sitting in a spreadsheet for example. then you may go through a Table file wizard transformation when loading. Your script may then look like this:

Table1:

let zUnit='A';

LOAD

     '$(zUnit)' as Unit,

     Key1,

     Key2,

     Key3

FROM

[test1.xls]

(biff, embedded labels, table is Sheet1$, filters(

Transpose(),

Remove(Row, Pos(Top, 1)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 1))

));

let zUnit='B';

load

     '$(zUnit)' as Unit,

     Key1,

     Key2,

     Key3

     Resident Table1;

.....

and so on.

I have included an example here:

[#4]

SBaldwin,

Thank you ever so much ... That is a fantastic solution which I will modify a bit for my purposes.

So, to answer your question ...

The data is held within an excel sheet and relates to risks, it is relatively unstructured and the key/value pairing contains string responses.  Some can be mapped to numeric values, but others can not. To make matters a little bit more complicated, I can have duplicate "keys" perhaps with a value, perhaps not.  Oh joy!

The ultimate reason for the transformation is that I believe that the end users will have an easier time creating their own charts rather than creating formulae in charts to achieve the same goals.

thanks again for both the ideas, and once I have something working properly, I'll update the post.