iterate over list of fields and apply same macro logic

[Eric Graves]

I think `iterate` is an overloaded term here. I am referring to it in the general sense of looping over a collection.

I have a list of 300 fields. For each field I need to recode any -1 values to an empty string. How can I do this programatically without having to write 300 definitions?

I have searched the documentation looking for control-flow keywords in the base language as well as the macro and template languages but don't see any obvious path for what seems like a fairly common data munging task.

[hwatanuki]

Hello Eric,

If I got your scenario description right, I think that this can be accomplished by using some of the ECL template language statements as exemplified in the code snippet below:

Code: Select all

myrec := RECORD
  integer4 rec_id;
  integer4 instance_id;
  integer4 first_date_received;
  integer4 last_date_received;
END;

myds := DATASET([{100,-1,20190101,20191231},
                 {101,10002,-1,20201231},
                 {103,10003,20190101,-1},
                 {104,10004,20190303,20191231}],myrec);

#EXPORTXML(myrecords,myrec);
#DECLARE(projStr);
#SET(projstr,'myrec mytransf(myds L) := TRANSFORM\n');
#FOR (myrecords);
  #FOR (field);
    #APPEND(projstr,'  SELF.' + %'{@label}'% + ' := IF(L.' + %'{@label}'% + '=-1,0,L.'+ %'{@label}'%+');\n');
  #END
#END;
#APPEND(projstr,'END;\n myproj := PROJECT(myds,mytransf(LEFT));\n');

%projstr%;
GenCode :=  %'projstr'%;

OUTPUT(GenCode);
OUTPUT(myproj);


In this example, the RECORD structure is being exported and loaded as a XML stream (#EXPORTXML) and then the field labels in this XML string are being parsed using a loop iteration (#FOR) to properly generate the PROJECT/TRANSFORM code containing the value replacement logic in every field.

Please let me know in case this is what you are looking for.

HTH,
HugoW

[rtaylor]

Eric,

Yes, this is a common issue. The problem is, since ECL is a declarative and non-procedural language, it cannot have the type of looping syntax you're used to in imperative languages. That's why we have all the operations that use TRANSFORM functions (PROJECT, ITERATE, etc.).

If you have only a few fields to do this on, then using PROJECT and just writing the transformation definitions to handle it is how it's usually done. Like this (I'm presuming STRING fields, because that's the only field type that makes sense to replace -1 with blank):

Code: Select all

filelayout := RECORD
  UNSIGNED myid;
  STRING10 f1;
  REAL f2;
  STRING10 f3;
  STRING10 f4;
END;

inds := DATASET([{1, '11.0', -1, '13.0', '99.0'},
                 {2, '-1', 22.0, '-1', '24.0'},
                 {3, '31.0', 32.0, '33.0', '-1'},
                 {4, '41.0', -1, '-1', '44.0'}
                 ],filelayout);
PROJECT(inds,
        TRANSFORM(RECORDOF(inds),
                  SELF.F1 := IF(LEFT.F1='-1','',LEFT.F1),
                  SELF.F3 := IF(LEFT.F3='-1','',LEFT.F3),
                  SELF.F4 := IF(LEFT.F4='-1','',LEFT.F4),
                  SELF := LEFT));

In fact, I've written this type of one-off code many times with many more fields. I generally do it by using a text editor with good macro capabilities to write the "boiler plate" lines for me.

But for a more generic approach, ECL's Template Language is the best approach, because it's all about generating "boiler plate" code.

So here's a MACRO that uses Template Language to do just that:

Code: Select all

MAC_ReplaceNegOneWithBlanks(ds,thislayout,resultname) := MACRO
     #EXPORTXML(Fred,thislayout);
     #DECLARE(ProjStr)
     #DECLARE (Ndx)
     #SET (Ndx, 0);           
     #FOR (Fred)
      #FOR (Field)
        #SET (Ndx, %Ndx% + 1)
        #IF ( %Ndx% = 1)
           #SET(ProjStr,'RECORDOF(' + ds + ') XF(' + ds + ' L) := TRANSFORM\n')
        #END
        #IF (%'{@type}'% = 'string')
          #APPEND(projstr,'  SELF.' + %'{@label}'% + ' := IF(L.' + %'{@label}'% + '=\'-1\',\'\',L.' + %'{@label}'% + ');\n');
        #END
      #END
     #END
     #APPEND(projstr,'  SELF := L;\nEND;\n' + resultname + ' := PROJECT(' + ds + ',XF(LEFT));\n');
     %projstr%;
      //%projstr% generates the code, while this:   
      // OUTPUT(%'projstr'%);   //shows us the generated code
ENDMACRO;

This MACRO uses #EXPORTXML to generate an XML string from the RECORD structure of the dataset you want to use. Template Language is designed to parse XML and generate ECL code from that. In this case, for my test file, that generated code would be this:

Code: Select all

            RECORDOF(inds) XF(inds L) := TRANSFORM
               SELF.f1 := IF(L.f1='-1','',L.f1);
               SELF.f3 := IF(L.f3='-1','',L.f3);
               SELF.f4 := IF(L.f4='-1','',L.f4);
               SELF := L;
            END;
            Res := PROJECT(inds,XF(LEFT));


So that, when I call the MACRO for my test dataset like this:

Code: Select all

MAC_ReplaceNegOneWithBlanks('inds',filelayout,'Res');

OUTPUT(Res);   //runs the generated code to produce the result


I get the result from that generated code that looks like this:

Code: Select all

1   11.0         -1.0   13.0         99.0     
2                22.0                24.0     
3   31.0         32.0   33.0                  
4   41.0         -1.0                44.0     


HTH,

Richard

[rtaylor]

Eric,

And just to finally "ice the cake"

Here's an improved MACRO that handles any type field and any search/replace values:

Code: Select all

MAC_ReplaceVals(ds,thislayout,resultname,FldType,SrchVal,ReplVal) := MACRO
     #UNIQUENAME(XF)
     #EXPORTXML(Fred,thislayout);
     #DECLARE(ProjStr)
     #DECLARE (Ndx)
     #SET (Ndx, 0);           
     #FOR (Fred)
      #FOR (Field)
        #SET (Ndx, %Ndx% + 1)
        #IF ( %Ndx% = 1)
           #SET(ProjStr,'RECORDOF(' + ds + ') ' + %'XF'% + '(' + ds + ' L) := TRANSFORM\n')
        #END
        #IF (%'{@type}'% = FldType)
          #IF (%'{@type}'% = 'string')
            #APPEND(projstr,'  SELF.' + %'{@label}'% + ' := IF(L.' + %'{@label}'% + '=\'' + SrchVal + '\',\'\',L.' + %'{@label}'% + ');\n');
          #ELSE
            #APPEND(projstr,'  SELF.' + %'{@label}'% + ' := IF(L.' + %'{@label}'% + '=' + SrchVal + ',' + ReplVal + ',L.' + %'{@label}'% + ');\n');
          #END
        #END
      #END
     #END
     #APPEND(projstr,'  SELF := L;\nEND;\n' + resultname + ' := PROJECT(' + ds + ',' + %'XF'% + '(LEFT));\n');
     %projstr%;
      
      // OUTPUT(%'projstr'%);   
ENDMACRO;      

MAC_ReplaceVals('inds',filelayout,'Res1','string','-1','');
MAC_ReplaceVals('inds',filelayout,'Res2','real',-1,-9999);

OUTPUT(Res1);   //runs the generated code to produce the result
OUTPUT(Res2);   //runs the generated code to produce the result

Of course, my demo uses the same test dataset as the previous.

HTH,

Richard

[Eric Graves]

Thank you both. I think the piece I was missing in reading the documentation was the connection between the template #FOR working on XML and the #EXPORTXML to generate XML from a record layout. With your clear, self-contained examples I was able to make progress.

[jwilt]

An alternate approach that *may* (or may not) be more clear to some developers... which does similar string-handling (under the hood), I suspect, but uses TOXML() and FROMXML() to string-ify rows for brute-force string replacement:

Code: Select all

lRando := {STRING f1; STRING f2; STRING f3;};
dsRando := DATASET([
  {'1', '-1', '-1'}
, {'-1', '2', '-1'}
, {'-1', '-1', '3'}
, {'4', '4', '4'}
], lRando);
lRando xfFixIt(dsRando Lf) := TRANSFORM
  strRec := '<Row>' + (STRING)TOXML(ROW(Lf, lRando)) + '</Row>';
  strFixed := STD.Str.FindReplace( strRec, '>-1<', '');
  self := FROMXML(lRando, strFixed);
END;
dsFixed := PROJECT(dsRando, xfFixIt(LEFT));

For what that's worth. Not sure this completely replaces the logic in your examples, but you get the gist. Template language can be hard to write. And read. And understand.

[rtaylor]

Jim,

Good alternative!!

One minor correction, this line:

Code: Select all

  strFixed := STD.Str.FindReplace( strRec, '>-1<', '');

needs to change to this:

Code: Select all

  strFixed := STD.Str.FindReplace( strRec, '>-1<', '><');

otherwise your XML becomes malformed and the FROMXML() won't work.

HTH,

Richard