Sat Oct 20, 2018 5:21 am
Login Register Lost Password? Contact Us


Use of TABLE Functionality

Comments and questions related to the Enterprise Control Language

Mon Oct 22, 2012 6:45 am Change Time Zone

Hi,

As my understanding we use TABLE to get particular fields(columns) of Recordset or dataset. i.e vertical slice.

But i want something like, an xml file, which contains fields(columns) of interest and use this file in TABLE, so that when fields need to be changed, i dont've to change the code just the xml file.

I tried using #EXPAND in macro but it didnt work. One of you people replied it can't be done in TABLE.

Is there any other way with or without TABLE?


Thank You,
Pradeep
Pradeep
 
Posts: 20
Joined: Mon Oct 22, 2012 6:18 am

Mon Oct 22, 2012 8:12 pm Change Time Zone

I think that a PROJECT is also a good alternative to a TABLE. I used it to read a very complex XML file and just slice the data that I needed from it.

Look at the following code example for more details:

http://hpccsystems.com/community/contributions/data-descriptors-and-simple-example-programs/musicmoz-artists-albums-and-tra

Regards,

Bob
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 975
Joined: Wed Jun 29, 2011 7:13 pm

Tue Oct 23, 2012 7:17 am Change Time Zone

Hi Bob,

Thanks for the reply.

I read your Clean_BandsArtists.txt, what i understood from that is you're reading complex xml and extracting artist from that, is that correct?

But what i'm trying is Dynamic struture for TABLE/Project!!
i.e TABLE/PROJECT takes 2nd param as "format", where you mention output record struture or you mention like explicit field names {name,Address,email}.

Now these 3 fields{name,Address,email} are coming from xml, and table should output data related to 3 fields and if i add "cellphone" in xml {name,Address,email, cellphone} table should output data of 4 fields.

My Files are

XML File
Code: Select all
- <configuration>
  <add Key="RiskAge,RiskSICCode,RiskValue" />
  </configuration>


ECL Code
Code: Select all
ConfigData := DATASET('~file::172.20.104.226::home:: user ::hpcc::lz_data::xml::config.xml',{STRING xmlstring {XPATH('<>')}}, XML('configuration/add'));

PolicyData := DATASET('~accurusi_poc::output::policydatacsv',$.common.Layout_policyDataRec,csv);

   outrec := RECORD
   String Key := XMLTEXT('@Key');
   END;

   parsedXML := PARSE(ConfigData,xmlstring,outrec,XML('add'));

   STRING str:=parsedXML[1].key;

   RequiredDS := TABLE(PolicyData,{#EXPAND(str)});

    OUTPUT(RequiredDS);



Right now it is giving "constant expression error"

and if use like this

Code: Select all
RequiredDS := TABLE(PolicyData,{#EXPAND('RiskAge,RiskSICCode,RiskValue')});

then it's working fine!!

Just wanna know is there any other way of doing this?

Am i using #EXPAND incorrectly?

Thank you,
Pradeep
Pradeep
 
Posts: 20
Joined: Mon Oct 22, 2012 6:18 am

Tue Oct 23, 2012 5:37 pm Change Time Zone

Pradeep,
I tried using #EXPAND in macro but it didnt work. One of you people replied it can't be done in TABLE.
What I said was that it can't be done in a FUNCTIONMACRO, because #EXPAND expects a constant string and not a variable value from a file -- your problem is not with the TABLE function but with #EXPAND. http://hpccsystems.com/bb/viewtopic.php?f=8&t=535&hilit=+TABLE&sid=1eb42fd0e5caaef85d1b43038d97907e

If the purpose here is to have exactly one place to change the fields to return when/if you want to change them, then I submit that the TABLE code itself is the right and proper place to do that, and not an external xml file read at runtime. Alternatively, you could define the RECORD structure for this TABLE function as a separate definition in its own file and have that be the one place it is changed/maintained.

However, if the purpose here is to dynamically define what fields are returned each time its called, and that is going to change multiple times within the same workunit, then I would submit that the call to the FUNCTIONMACRO is the right and proper place to do that, passing in a different constant string each time it is called (and being a constant string, the #EXPAND will then operate the way you want it to).

Or is there some overriding reason this information has to come from an xml file? Is this a design issue that someone unfamiliar with HPCC has imposed?

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

Thu Oct 25, 2012 7:18 am Change Time Zone

Richard,

However, if the purpose here is to dynamically define what fields are returned each time its called, and that is going to change multiple times within the same workunit.
This is exacltly what i want and you're sayin FUCNTIONMACRO is right option!!



passing in a different constant string each time it is called (and being a constant string, the #EXPAND will then operate the way you want it to).

But this is where i'm gettin stuck "passing in constant string each time it is called"

Has anyone done like this, or is it possible using FUCNTIONMACRO?
Or am i wasting my time in doing this :? ?

Thank You,
Pradeep
Pradeep
 
Posts: 20
Joined: Mon Oct 22, 2012 6:18 am

Thu Oct 25, 2012 3:40 pm Change Time Zone

Pradeep,
But this is where i'm gettin stuck "passing in constant string each time it is called"
The operative term here is "constant string." You do that like this:
Code: Select all
Rec := RECORD
  UNSIGNED1 ID;
   STRING1   F1;
   STRING1   F2;
   STRING1   F3;
   STRING1   F4;
END;
DS := DATASET([{1,'A','B','C','D'},
               {2,'E','F','G','H'},
               {3,'I','J','K','L'}],Rec);
DSflds := DATASET([{'ID,F1,F2'},{'ID,F2,F3'},{'ID,F3,F4'}],{STRING list});

FM_Vslice(RecSet,fields) := FUNCTIONMACRO
  t := TABLE(RecSet,{#EXPAND(fields)});
   RETURN t;
ENDMACRO;

// FM_Vslice(DS,DSflds[1].list);
// FM_Vslice(DS,DSflds[2].list);
// FM_Vslice(DS,DSflds[3].list);
FM_Vslice(DS,'ID,F1,F2');
FM_Vslice(DS,'ID,F2,F3');
FM_Vslice(DS,'ID,F3,F4');
The commented out code does not work, creating a "constant expression expected" error, which is solved by the uncommented version that passes the field list as a string constant (not as a variable field value). That's the difference between trying to pass in a variable containing text and passing in a constant string of that text.

So, it's back to the "drawing board" and time to ask once again -- what task are you actually trying to accomplish? Your first-choice solution to the problem (trying to generate a different TABLE each time) simply won't work unless you write your own external ECL code-generation program to write that code outside of the HPCC environment and then run it using ECLplus.exe, so we need to find another way to solve the actual problem you're trying to solve. IOW, what's the "big picture" problem we're solving?

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

Fri Oct 26, 2012 11:19 am Change Time Zone

Richard,

Thank You for detailed reply. :)
it's back to the "drawing board" and time to ask once again -- what task are you actually trying to accomplish?
The Task is I'm getting data which contains around 15 to 20 columns. After spraying and reading, i need only few columns like age, location etc. so that i can apply clustering using Machine Learning(ML) library.

So i used TABLE to get columns/fields of interest from dataset(which contains 15 to 20 columns) and it worked fine.

Now the requirement is like, i should not hard code fields of interest. They should come from an external file like XML because fields will change, so i tried this Functionmacro and #expand. Passing constant string 'a, b, c' to macro is same like mentioning fields in TABLE(recordset, {field1, field2, filed3}) which we dont want.

This is the big picture!! :roll:

Thank You,
Pradeep
Pradeep
 
Posts: 20
Joined: Mon Oct 22, 2012 6:18 am

Fri Oct 26, 2012 1:42 pm Change Time Zone

Pradeep,
Now the requirement is like, i should not hard code fields of interest. They should come from an external file like XML because fields will change, so i tried this Functionmacro and #expand. Passing constant string 'a, b, c' to macro is same like mentioning fields in TABLE(recordset, {field1, field2, filed3}) which we dont want.
OK, I understand the issue -- you want to be able to change the set of interesting fields and do it in just one place. But what I'm not getting from your description is exactly how often those fields will change. Will that be daily? Hourly? Monthly?

I can understand your desire to have a single place to update, whenever you want to change the fields. But unless that "single place to update" is used by multiple platforms I see absolutely no reason why it must be an external XML file. Updating a single ECL definition file is exactly the same amount of work as updating the content of an XML file -- they are both, after all, simply text files.

In this example, SetFldList is a local definition, but in a production code-base I would make it a separate EXPORT definition (a single .ecl file), maintainable as easily as an external XML file:
Code: Select all
Rec := RECORD
  UNSIGNED1 ID;
   STRING1   F1;
   STRING1   F2;
   STRING1   F3;
   STRING1   F4;
END;
DS := DATASET([{1,'A','B','C','D'},
               {2,'E','F','G','H'},
               {3,'I','J','K','L'}],Rec);
DSflds := DATASET([{'ID,F1,F2'},{'ID,F2,F3'},{'ID,F3,F4'}],{STRING list});

FM_Vslice(RecSet,fields) := FUNCTIONMACRO
  t := TABLE(RecSet,{#EXPAND(fields)});
  RETURN t;
ENDMACRO;

SetFldList := ['ID,F1,F2','ID,F2,F3','ID,F3,F4'];
FM_Vslice(DS,SetFldList[1]);
FM_Vslice(DS,SetFldList[2]);
FM_Vslice(DS,SetFldList[3]);

So, my next question is -- MUST it be an external XML file? And, if so, why?

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

Sun Oct 28, 2012 11:26 am Change Time Zone

Richard,
SetFldList is a local definition, but in a production code-base I would make it a separate EXPORT definition (a single .ecl file), maintainable as easily as an external XML file:

Making separate ecl file and then exporting definition is fine, i've tried previously
and it works.

Frequency of fields getting changed is Monthly!!

Yes it must be an external xml file.
But why an xml file? for this right now i dont've answer. :?

Thank You,
Pradeep
Pradeep
 
Posts: 20
Joined: Mon Oct 22, 2012 6:18 am

Sun Oct 28, 2012 2:05 pm Change Time Zone

Pradeep,
Frequency of fields getting changed is Monthly!!

Yes it must be an external xml file.
But why an xml file? for this right now i dont've answer.
Since the frequency is only once a Month, then I suggest that you simply change the ECL code at the same time that you update the XML file (presumably this XML file will be used by some other system) -- that way you satisfy both requirements.

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

Next

Return to ECL

Who is online

Users browsing this forum: No registered users and 1 guest

cron