# Variable merged into data?

**URL:** <https://forum.cloverdx.com/t/variable-merged-into-data/537>\
**Category:** CloverDX Platform\
**Created:** [June 5, 2009, 12:00am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537 "2009-06-05T00:00:00Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![deshartman](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/deshartman/32/87_2.png) [@deshartman](https://forum.cloverdx.com/u/deshartman)\
**Post date:** [June 5, 2009, 12:00am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/1 "2009-06-05T00:00:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 5, 2009, 12:38pm UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/2 "2009-06-05T12:38:31Z")

</div>

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

```auto
<?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>

```

---

<div class="post-metadata">

**Author:** ![deshartman](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/deshartman/32/87_2.png) [@deshartman](https://forum.cloverdx.com/u/deshartman)\
**Post date:** [June 6, 2009, 12:36am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/3 "2009-06-06T00:36:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![deshartman](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/deshartman/32/87_2.png) [@deshartman](https://forum.cloverdx.com/u/deshartman)\
**Post date:** [June 8, 2009, 1:38am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/4 "2009-06-08T01:38:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 9, 2009, 9:04am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/5 "2009-06-09T09:04:06Z")

</div>

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:

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

```

while parameters are substituted by theirs values during graph instantiation.

---

<div class="post-metadata">

**Author:** ![deshartman](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/deshartman/32/87_2.png) [@deshartman](https://forum.cloverdx.com/u/deshartman)\
**Post date:** [June 9, 2009, 10:42am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/6 "2009-06-09T10:42:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 9, 2009, 11:28am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/7 "2009-06-09T11:28:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![deshartman](https://yyz1.discourse-cdn.com/flex007/user_avatar/forum.cloverdx.com/deshartman/32/87_2.png) [@deshartman](https://forum.cloverdx.com/u/deshartman)\
**Post date:** [June 9, 2009, 2:54pm UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/8 "2009-06-09T14:54:29Z")

</div>

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

Thanks  
Des

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [June 10, 2009, 9:21am UTC](https://forum.cloverdx.com/t/variable-merged-into-data/537/9 "2009-06-10T09:21:10Z")

</div>

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

```auto
$0.supplier_file_id := ${SUPPLIER_ID};

```

And the graph “see” it as:

```auto
$0.supplier_file_id := 0;

```