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.
(biff, embedded labels, table is Sheet1$);
sum(value) as value,
autonumber(unit,'unit') as auto_unit,
autonumber(key,'key') as auto_key
auto_unit &'_' & auto_key as link,
autonumber(unit,'unit') as unit ,
unit as value
autonumber(key,'key') as key ,
key as value
// 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) ;
drop table SOURCE;
drop table sb;
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:
'$(zUnit)' as Unit,
(biff, embedded labels, table is Sheet1$, filters(
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))
'$(zUnit)' as Unit,
and so on.
I have included an example here:
Test1.zip 126.8 K
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.