Variable merged into data?

CloverDX Platform
1

I’m trying to set up a graph to do the following:

  1. Read from dB and get parameters
    XLSFileName
    CustomerID
  2. I then pass the XLSFileName into an XLSReader that reads the data and presents a meta stream of XLSData.
  3. I now need to merge the CustomerID value with the values from the XLS sheet, by adding this as the last column of the data. The CustomerID is fixed and thus needs to be repeated for each row.

Rough graph looks like this:

----[dBMeta]— — [XLSMeta] ---- < Merge???> — [MergedMeta] — OUTPUT
| |
±----------- [dBMeta] ---------------------------------------+

dBMeta
XLSURL, CustomerID

XLSMeta
First, Last

=================
dBData
XLSFile, 25

XLSData
Bob, Jones
Bill, Smith

Desired Resulting table…

First, Last, CustomerID
Bob, Jones, 25
Bill, Smith, 25

Is there a way to do this with a Join or merge of some description. Since I do not know what the customer ID is going to be until I query the dB, I cannot fix it.

Alternatively is there a way to write the CustomerID into a variable and then read the variable as part of the final Meta? Really would appreciate help here.

Thanks
Des

2

Hello Des,
I’m not sure if I’ve understood the task properly, but the following graph should process required transformation:

<?xml version="1.0" encoding="UTF-8"?>
<Graph name="xlsDb" >
<Global>
<Metadata id="Metadata0">
<Record fieldDelimiter="|" name="dbMeta" recordDelimiter="\n" type="delimited">
<Field name="XLSURL" type="string"/>
<Field name="CustomerID" type="long"/>
</Record>
</Metadata>
<Metadata id="Metadata2">
<Record fieldDelimiter="|" name="result" recordDelimiter="\n" type="delimited">
<Field name="First" type="string"/>
<Field name="Last" type="string"/>
<Field name="CustomerId" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata1">
<Record fieldDelimiter="|" name="xlsMeta" recordDelimiter="\n" type="delimited">
<Field name="First" type="string"/>
<Field name="Last" type="string"/>
<Field auto_filling="source_name" name="fileURL" type="string"/>
</Record>
</Metadata>
<Connection dbConfig="conn/conn.cfg" id="Connection0" type="JDBC"/>
<Property fileURL="workspace.prm" id="GraphParameter0"/>
</Global>
<Phase number="0">
<Node dbConnection="Connection0" id="DB_INPUT_TABLE0" sqlQuery="select XLSURL, CustomerID from dbdata" type="DB_INPUT_TABLE"/>
<Node id="EXT_HASH_JOIN0" joinKey="$fileURL=$XLSURL;#" type="EXT_HASH_JOIN">
<attr name="transform"><![CDATA[//#TL

// Transforms input record into output record.
function transform() {
	$0.First := $0.First;
	$0.Last := $0.Last;
	$0.CustomerId := $1.CustomerID;
}

]]></attr>
</Node>
<Node id="SIMPLE_COPY0" type="SIMPLE_COPY"/>
<Node id="TRASH0" type="TRASH"/>
<Node fileURL="port:$0.XLSURL:source" id="XLS_READER0" type="XLS_READER"/>
<Edge fromNode="DB_INPUT_TABLE0:0" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="SIMPLE_COPY0:0"/>
<Edge fromNode="EXT_HASH_JOIN0:0" id="Edge5" inPort="Port 0 (in)" metadata="Metadata2" outPort="Port 0 (out)" toNode="TRASH0:0"/>
<Edge fromNode="SIMPLE_COPY0:0"  id="Edge1" inPort="Port 0 (input)" metadata="Metadata0" outPort="Port 0 (out)" toNode="XLS_READER0:0"/>
<Edge fromNode="SIMPLE_COPY0:1" id="Edge4" inPort="Port 1 (slave)" metadata="Metadata0" outPort="Port 1 (out)" toNode="EXT_HASH_JOIN0:1"/>
<Edge fromNode="XLS_READER0:0"  id="Edge3" inPort="Port 0 (driver)" metadata="Metadata1" outPort="Port 0 (out)" toNode="EXT_HASH_JOIN0:0"/>
</Phase>
</Graph>
3

Agata,

I’ll try it today and see if it does the trick.

Basically I need to insert a “constant” in an extra column off the XLS data. I’ll try it and let you know.

As always thanks for the help

Des

4

Nope, did not do the job. Problem is I do not have a common key to merge with.

Let me ask the question differently. Is there a way to assign a parameter value to the data? Something like this

// Transforms input record into output record.
function transform() {
$0.supplier_file_id := ${SUPPLIER_ID};

In this way I can read the dB and get the value I want and then using a Reformat transform, I can assign the parameter to the value in the transform.

I tried this, but get a syntax error.

Thanks
Des

5

Hi Des,
if supplier_file_id field is numeric your transformation should work (what message do you get?), if it is string data field, all you need is to quote it:

$0.supplier_file_id := '${SUPPLIER_ID}';

while parameters are substituted by theirs values during graph instantiation.

6

Agata

Yep, that did the trick. If I do not have the ’ 's I get a syntax error complaining about the “$” value.

I am unable to set the type of the parameter, so I presume all parameters are String? Here is an extract from the Source

Thanks for the help again.

Des

7

If you know this is the number it is better to use a numeric type, while most of the operations is faster on numbers than on strings.

8

It is a number, so how would I set up a parameter as numeric type?

Thanks
Des

9

Nothing ;-). If data field is numeric, just write in your transformation:

$0.supplier_file_id := ${SUPPLIER_ID};

And the graph “see” it as:

$0.supplier_file_id := 0;