topic Re: pivot key value pairs in load in QlikView

pivot key value pairs in load

Thu, 18 Aug 2011 09:32:45 GMT

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.

Re: pivot key value pairs in load

sbaldwin — Thu, 18 Aug 2011 12:38:26 GMT

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;

Re: pivot key value pairs in load

Thu, 18 Aug 2011 13:25:57 GMT

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:

Re: pivot key value pairs in load

Fri, 19 Aug 2011 05:56:11 GMT

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.