Dynamic Join/Sort keys

Support/help with CloverETL implementation problems

jblythe99
Posts: 14
Joined: Mon Apr 20, 2015 3:14 pm

Dynamic Join/Sort keys

Postby jblythe99 » Wed Sep 27, 2017 5:40 pm

Hello again-

Is there a way to pass the join/sort keys as dictionary values? I can probably accomplish what I am trying to do if I take my graph and run it through a "Run Graph" component, but I am trying to avoid that is possible. What I am trying to accomplish is to generate the text of the sort and join based on the values of the headers in the metadata. I want to be able to use this one graph to unit test. I can generate the values of the dictionary on the fly using a reformat step, I just cannot use them right now as they are not available to the graph at run time (aka via a parameter). I understand this may not be possible right now but I thought I would pose the question. Thanks.
Attachments
example_screen.jpg
example_screen.jpg (21.42 KiB) Viewed 93 times

bartonv
Posts: 24
Joined: Wed May 03, 2017 12:10 pm

Re: Dynamic Join/Sort keys

Postby bartonv » Wed Oct 04, 2017 3:20 pm

Hi jblythe99,
as you rightly indicated, CloverETL is currently not designed in a way that would enable sorting by dictionary values. Even though your post poses a lot of additional questions, I dare to presume you could take advantage of the dynamic parameter functionality to fulfill the same purpose. Let me demonstrate defining the dynamic parameter in this example of a parameter definition (Parameters > new parameter > Value > Convert to Dynamic):

Code: Select all

//#CTL2
function string getValue() {
   myMetadata dummyRecord;
   return getFieldName(dummyRecord, 2);
}

Let’s assume that you have metadata called “myMetadata” in your graph with fields: ‘id’, ‘customerName’, ‘address1’, ‘address2’. The dynamic parameter definition would assign the individual field names to a temp field called “dummyRecord”. Then it would get the field name of the third column from the myMetadata (the fields are indexed from 0, so “2” actually represents the third column) which is ‘address1’. You can then assign this parameter for example as the sort key in the ExtSort component.
When you run the graph, the sort key would be determined in the initialization phase of the graph run even if the metadata is dynamic (unknown at the graph runtime). If using dynamic parameters is not a viable option for you, please attach your graph and provide more details concerning your business reasons.
Now let’s take a closer look at the “Run Graph” concept that you mentioned in your update. The dynamic parameter concept presented above would address your issue if the metadata could be retrieved in the beginning of the graph run. The “Run Graph” concept, on the contrary, would be a better candidate if this was not the case. Note: actually, I would suggest using a cascade of components in a jobflow instead of the RunGraph component. You would want to have the first part of the cascade (which could be a single component or a couple of components or even an ExecuteGraph component) run solely for the purpose of obtaining the join/sort keys and passing them as parameters to the second part of the cascade which would be an ExecuteGraph component that would run the actual graph of yours.
Best regards,
---
Vladimir Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

jblythe99
Posts: 14
Joined: Mon Apr 20, 2015 3:14 pm

Re: Dynamic Join/Sort keys

Postby jblythe99 » Wed Oct 04, 2017 4:29 pm

Thank you for the response. This will work in most cases except for one, at least currently (we think). Just a note up front, we dont have the ability to run jobflows as our server license is not at that level.

Second, my most recent post alludes to one point related to your response. We would like to have a more dynamic way of testing our processes so we have moved most of the processing to separate subgraphs that contain 1 input and 1 output (we then just string them together). Because of this, the metadata will change as the processes is tested, but in a predictable manner. We can define the metadata dynamically from the database, however we cannot seem to reference that metadata in CTL. If this were a possibility then your solution would work flawlessly I believe. Am I able to reference dynamic metadata in CTL?

vazquezrosariop
Posts: 101
Joined: Mon Feb 29, 2016 5:33 pm

Re: Dynamic Join/Sort keys

Postby vazquezrosariop » Mon Oct 09, 2017 2:26 pm

The question was answered on the following forum thread:

http://forum.cloveretl.com/viewtopic.php?f=4&t=7505
---
Pedro Vazquez Rosario
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

jblythe99
Posts: 14
Joined: Mon Apr 20, 2015 3:14 pm

Re: Dynamic Join/Sort keys

Postby jblythe99 » Tue Oct 10, 2017 6:27 pm

yup that was my original question and it fixed my issue. Thank you!