Wed Dec 12, 2018 8:57 pm
Login Register Lost Password? Contact Us


Anyone written an generalized TRANSPOSE MACRO?

Questions around writing code and queries

Sat Oct 06, 2018 12:50 pm Change Time Zone

Hi,

Anyone written a genaralised TRANSPOSE MACRO (FUNCTIONMACRO)?
Something that rotates a DATASET. See Excel TRANSPOSE for a description of what I'm after.

Yours

Allan
Allan
 
Posts: 322
Joined: Sat Oct 01, 2011 7:26 pm

Mon Oct 08, 2018 7:17 pm Change Time Zone

Allan,

Interesting problem. I don't know of anyone that's done this, but here's my first take on it. Note that I'm assuming that CSV-style output will be sufficient. Here's my FUNCTION and FUNCTIONMACRO that do all the work:
Code: Select all
RecFromSet(SET OF STRING s) := FUNCTION //rolls a set of strings to a single string
  OutRec := {STRING out};
  ds := DATASET(s,OutRec);
  RETURN ROLLUP(ds,1=1,
                TRANSFORM(OutRec,SELF.out := TRIM(LEFT.out) + ',' + RIGHT.out))[1].out;
END;

Transpose(ds) := FUNCTIONMACRO                     
  #EXPORTXML(Struct,ds);   //generate xml structure to parse
  #DECLARE(code1);         //create "code1" symbol
  #SET(code1,'FldSet := [');  //initialize
  #DECLARE(code2);         
  #SET(code2,'');           
  #DECLARE(code3);         
  #SET(code3,'DATASET(FldCnt,TRANSFORM({STRING Fld},\n  SELF.Fld := CHOOSE(COUNTER,'); 
  #DECLARE(ctr);           
  #SET(ctr,0);              //initialize to 0
  #FOR (Struct)             
    #FOR (field)            //for each "Field" tag in the generated xml
      #SET(ctr,%ctr% + 1);  //increment the "ctr" symbol
      #IF (%ctr% = 1)       //and detect first iteration
        #APPEND(code1,'\'' + %'{@name}'% + '\'' );
        #APPEND(code3,'\n    FldSet[COUNTER]  + \',\' + RecFromSet(' +
                      %'{@name}'% +'_set)');
      #ELSE
         #APPEND(code1,',\'' + %'{@name}'% + '\'' );
         #APPEND(code3,',\n    FldSet[COUNTER]  + \',\' + RecFromSet(' +
                         %'{@name}'% +'_set)');
      #END
      #APPEND(code2,%'{@name}'% +'_set := SET(' + #TEXT(ds) + ',(STRING)' +
                    %'{@name}'% + ');\n');
    #END
  #END
  #APPEND(code1,'];\nFldCnt := COUNT(FldSet);\n');
  #APPEND(code3,')));\n');

  // RETURN %'code1'% + %'code2'% + %'code3'% ; //just to look at generated code
  %code1%    //generates the actual code
  %code2%
  RETURN %code3%
ENDMACRO;

Then I call the FUNCTIONMACRO to generate the code and do the work:
Code: Select all
ds1 := DATASET([{'March',1,4,7},
                {'April',2,5,8},
                {'May',3,6,9}],
               {STRING10 Month, UNSIGNED1 Jeff, UNSIGNED1 Fred, UNSIGNED1 Marty});
OUTPUT(ds1,NAMED('input'));  //look at input data
OUTPUT(Transpose(ds1),NAMED('Transposed'));

And this produces a transposed result that looks like this:
Code: Select all
month,March,April,May       
jeff,1,2,3
fred,4,5,6
marty,7,8,9

And now I'll have to go have "a think" and see if I can make it put everything into separate fields instead of CSV-style results. :)

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1396
Joined: Wed Oct 26, 2011 7:40 pm

Tue Oct 09, 2018 9:05 am Change Time Zone

Hi Richard,

Though I did not expect an answer from your, I kind of knew it would tickle your fancy.
Pressure of work means I won't be able to follow this up immediately, but 'I'll be back' to quote Arnold Schwarzenegger.

PS. Yes for me the result has to end up in a dataset.
When we (you) get this solid it could be placed on the 'tips and tricks' section of the forum as its of general use.

Cheers
Allan
Allan
 
Posts: 322
Joined: Sat Oct 01, 2011 7:26 pm

Tue Oct 09, 2018 4:57 pm Change Time Zone

Allan,
Yes for me the result has to end up in a dataset.
You can take the existing result and write it to disk -- it's a CSV file at that point. :)

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1396
Joined: Wed Oct 26, 2011 7:40 pm

Wed Oct 10, 2018 9:28 am Change Time Zone

Yes Richard,

Find for me, but I was thinking of a more self contained complete solution. One that could be uploaded to the 'Tips & Tricks' section of the forum.

I'm mulling over an implementation myself. I think any production version should have an option to name the output fields. As it stands post transpose, there is no mapping between old and new data sets field names.

Yours
Allan
Allan
 
Posts: 322
Joined: Sat Oct 01, 2011 7:26 pm

Thu Oct 11, 2018 10:29 am Change Time Zone

Richard,

Actually your TRANSPOSE works a treat! Just what I needed.
Possible gold coin winging your way.

Yours
Allan
Allan
 
Posts: 322
Joined: Sat Oct 01, 2011 7:26 pm

Thu Oct 11, 2018 4:41 pm Change Time Zone

:D
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1396
Joined: Wed Oct 26, 2011 7:40 pm

Fri Nov 30, 2018 3:28 pm Change Time Zone

Should be doable if there's a way to specify the RECORD definition properly. It's not too much trouble to hack the above into something that returns an inline dataset (see below) but the compiler is not happy about having the RECORD generated on the fly. Ideas? Would also mean I could solve an unstack function I've been troubling with.

Code: Select all
RecFromSet(SET OF STRING s) := FUNCTION //rolls a set of strings to a single string
  OutRec := {STRING out};
  ds := DATASET(s,OutRec);
   rolledDs := ROLLUP(ds, 1=1, TRANSFORM(OutRec, SELF.out := TRIM(LEFT.out) + '\',\'' + RIGHT.out))[1].out;
  RETURN '\'' + TRIM(rolledDs, LEFT, RIGHT) + '\'';
END;

Transpose(ds) := FUNCTIONMACRO                     
    #EXPORTXML(Struct,ds);   //generate xml structure to parse
    #DECLARE(recDef);
    #SET(recDef,'{');  //initialize
    #DECLARE(code1);         //create "code1" symbol
    #SET(code1,'FldSet := [');  //initialize
    #DECLARE(code2);         
    #SET(code2,'');           
    #DECLARE(code3);         
    #SET(code3,'DATASET('); 
    #DECLARE(ctr);           
    #SET(ctr,0);              //initialize to 0
    #FOR (Struct)             
      #FOR (field)            //for each "Field" tag in the generated xml
        #SET(ctr,%ctr% + 1);  //increment the "ctr" symbol
        #IF (%ctr% = 1)       //and detect first iteration
          #APPEND(code1,'\'' + %'{@name}'% + '\'' );
          #APPEND(code3,'\n [{#EXPAND(RecFromSet(' + %'{@name}'% +'_set))}');
                #APPEND(recDef, 'STRING x;')
        #ELSE
           #APPEND(code1,',\'' + %'{@name}'% + '\'' );
           #APPEND(code3,',\n{#EXPAND(RecFromSet(' + %'{@name}'% +'_set))}');
      #APPEND(recDef, 'STRING #EXPAND(' + %'{@name}'% + '_set[1]);')
        #END
        #APPEND(code2,%'{@name}'% +'_set := SET(' + #TEXT(ds) + ',(STRING)' + %'{@name}'% + ');\n');
      #END
    #END
    #APPEND(recDef,'}')
    #APPEND(code1,'];\nFldCnt := COUNT(FldSet);\n');
    #APPEND(code3,'], '+ %'recDef'% +' );\n');

    RETURN %'code1'% + %'code2'% + %'code3'% ; //just to look at generated code
    // %code1%    //generates the actual code
    // %code2%
    // RETURN %code3%
  ENDMACRO;


The above would also need the top row removing but you get the idea.
mansfield_bitter
 
Posts: 5
Joined: Tue Sep 13, 2016 8:13 am

Fri Nov 30, 2018 11:08 pm Change Time Zone

mansfield_bitter,

Thanks for getting me to re-visit this one, since I have now come up with a much better solution that generates the transposed dataset into separate fields instead of a single comma-delimited string. :)

Here's the code:
Code: Select all
Transpose(ds, recCnt) := FUNCTIONMACRO                     
    #EXPORTXML(Struct,ds);   //generate xml structure to parse

    #DECLARE(code1);         //create "code1" symbol
    #SET(code1,'FldSet := [');  //initialize
    #DECLARE(code2);         
    #SET(code2,'');      //set defs         
    #DECLARE(code3);         
    #SET(code3,'ChooseSet(UNSIGNED C) := CHOOSE(C');      //set chooser
    #DECLARE(code4);         
    #SET(code4,'OutRec := RECORD\n  STRING  Col0;\n');      //output RECORD structure
    #DECLARE(code5);         
    #SET(code5,'DATASET(FldCnt,\n  TRANSFORM(OutRec,\n  RecSet    := ChooseSet(COUNTER);\n  SELF.Col0 := fldSet[COUNTER]'); //DATASET to return
      
    #DECLARE(ctr);           
    #SET(ctr,0);              //initialize to 0
    #FOR (Struct)             
      #FOR (field)            //for each "Field" tag in the generated xml
        #SET(ctr,%ctr% + 1);  //increment the "ctr" symbol
        #IF (%ctr% = 1)       //and detect first iteration
          #APPEND(code1,'\'' + %'{@name}'% + '\'' );
        #ELSE
          #APPEND(code1,',\'' + %'{@name}'% + '\'' );
        #END
        #APPEND(code2,%'{@name}'% +'_set := SET(' + #TEXT(ds) + ',(STRING)' + %'{@name}'% + ');\n');
            #APPEND(code3,',' + %'{@name}'% +'_set');
      #END
    #END
    #APPEND(code1,'];\nFldCnt := COUNT(FldSet);\n');
    #APPEND(code3,');\n');

    #DECLARE(Ndx)
    #SET(Ndx, 1);            //initialize Ndx to 1
    #LOOP
      #IF(%Ndx% > recCnt)   
        #BREAK         // break out of the loop
      #ELSE             //otherwise
        #APPEND(code4,'  STRING  Col' + %'Ndx'% + ';\n');
        #APPEND(code5,',\n  SELF.Col' + %'Ndx'%  + ' := RecSet[' + %'Ndx'%  + ']');
        #SET (Ndx, %Ndx% + 1)
      #END
    #END
    #APPEND(code4,'END;\n');
    #APPEND(code5,'\n  ));\n');

    // RETURN %'code1'% + %'code2'% + %'code3'% + %'code4'% + %'code5'% ; //just to look at generated code
    %code1%    //generates the actual code
    %code2%
    %code3%
    %code4%
    RETURN %code5%
  ENDMACRO;

ds1 := DATASET([{'March',1,4,7},
                {'April',2,5,8},
                {'July',13,16,19},
                {'May',3,6,9},
                {'June',10,11,12}],
               {STRING10 Month, UNSIGNED1 Jeff, UNSIGNED1 Fred, UNSIGNED1 Marty});
OUTPUT(ds1,NAMED('input'));  //look at input data
OUTPUT(Transpose(ds1,COUNT(ds1)),NAMED('Transposed'));

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1396
Joined: Wed Oct 26, 2011 7:40 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron