iterate over list of fields and apply same macro logic
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.
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.
- Eric Graves
- Posts: 4
- Joined: Wed Feb 24, 2021 3:13 pm
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:
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
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
- hwatanuki
- Posts: 28
- Joined: Mon Apr 15, 2019 1:22 am
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):
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:
HTH,
Richard
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));
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;
- 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));
- Code: Select all
MAC_ReplaceNegOneWithBlanks('inds',filelayout,'Res');
OUTPUT(Res); //runs the generated code to produce the result
- 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
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
Eric,
And just to finally "ice the cake"
Here's an improved MACRO that handles any type field and any search/replace values:
HTH,
Richard
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
HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
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.
- Eric Graves
- Posts: 4
- Joined: Wed Feb 24, 2021 3:13 pm
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:
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.
- 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.
- jwilt
- Posts: 56
- Joined: Wed Feb 27, 2013 7:46 pm
Jim,
Good alternative!!
One minor correction, this line:
HTH,
Richard
Good alternative!!
One minor correction, this line:
- Code: Select all
strFixed := STD.Str.FindReplace( strRec, '>-1<', '');
- Code: Select all
strFixed := STD.Str.FindReplace( strRec, '>-1<', '><');

HTH,
Richard
- rtaylor
- Community Advisory Board Member
- Posts: 1619
- Joined: Wed Oct 26, 2011 7:40 pm
7 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 1 guest