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:
<dBReader> ----[dBMeta]--- <XLSReader> --- [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
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:
<dBReader> ----[dBMeta]--- <XLSReader> --- [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
-
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> -
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 -
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
<Property id="GraphParameter1" name="SUPPLIER_FILE_ID" value="0"/>
Thanks for the help again.
Des
Please sign in to leave a comment.
Comments 8