3 Replies Latest reply: Aug 19, 2011 2:08 AM by uri keen RSS

    pivot key value pairs in load

      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
          Steve Baldwin

          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
            Quentin Richard Bitegue-Bi-Essono

            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

              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.