topic Re: dynamically determine column names in Talend Studio

dynamically determine column names

Anonymous — Sat, 16 Nov 2024 03:47:57 GMT

I am trying to figure out how I can use 2 source files to produce a data that I can save to a database table (MySQL). The issue is that 1 file (XML) has the actual data but the second one (Excel) is a mapping of what fields should be used in the XML. Below is an example of what I have as a source files:

XML file:

Excel File:

When I process the XML I should match the DIVISIONNUMBER in the XML with the Division in the spreadsheet. Then get the data for defined DBTargetColumns from XMLSourceColumns (concatenate them all with a space as a delimiter character).

For example, based on the above sample files the logic should follow this rule for Division 03:

ProductName = DIVISIONNAME + " " + ITEMNAME

ItemNumber = ITEMNO

ColorName = COLORNAME

ColorCode = COLORCODE

And this will be the actual data result:

ProductName = "Division03 Product1 Name"

ItemNumber = "Product1"

ColorName = "BLACK"

ColorCode = "BK"

And then for Division 10 this would be the rule:

ProductName = DIVISIONNAME + " " + CUSTOMNAME + " " + ITEMNAME

ItemNumber = DIVISIONNUMBER + " " + ITEMNO

ColorName = CUSTOMCOLORNAME

ColorCode = DIVISIONNUMBER + " " + COLORCODE

The expected result:

ProductName = "Division10 Prod2 Alt. Name Product2 Name"

ItemNumber = "10 Product2"

ColorName = "RD Custom"

ColorCode = "10 R"

I'd appreciate any help with this problem.

Re: dynamically determine column names

tnewbie — Wed, 18 Dec 2019 01:48:56 GMT

Assuming you have limited (4 in this example) parameters to search for
Assuming your excel entries are hortizontally sequential and there is no gap in between, meaning your entry should not look like Column C is filled, Column D is empty, Column E is filled
1) You would call the connector link that flows after xmlextract as say carryfwdxl, this is to ensure that your links are not named as row1, row2 as these numbers might change on disconnecting and reconnecting
2) You would reconstruct the lookup file as Column A, Column B, Column c+ Column D+ Column E+.....Column n, i would call them hypothetically as col1, col2 and col3
3) You would need 4 lookups to the configuration file (Excel in this example)
First lookup would match the division number and product number on col1 and col2 and return col3
Second lookup would match the division number and ItemNumber on col1 and col2 and return col3
Third lookup would match the division number and ColorName on col1 and col2 and return col3
Fourth lookup would match the division number and ColorCode on col1 and col2 and return col3

Your return values from each of these lookups would look like DIVISIONNAME+CUSTOMNAME etcnow do a quick replace of + with "carryfwdxl."+, so the resultant value should look like
carryfwdxl.DIVISIONNAME+carryfwdxl.CUSTOMNAME. You can always insert spaces as you need in between these values

On the other hand if you too many parameters like product number, item number etc and if those keep growing or changing then i would suggest have a cartesian product prebuilt and lookup that data set, which i guess is not likely in your case.

Hope that helps

Re: dynamically determine column names

Anonymous — Wed, 18 Dec 2019 14:33:56 GMT

Thanks for your reply. Could you clarify where the lookup should be performed? Do I use a tMap or something else?

Re: dynamically determine column names

tnewbie — Fri, 20 Dec 2019 05:33:29 GMT

Yes...lookup using Tmap