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