Dynamic Schema for Mapping in tMap

Seven Stars

Dynamic Schema for Mapping in tMap

Hi, I have an XML file which I receive on daily basis but the schema is given as follow,

 

C1

C2

C3, where the value of C1, C2, C3 fluctuates it can be one of {Location, Date, and Confirmation Number}.

Example of raw file of different date shared below.

Schema_1.JPGSchema_2.JPGSchema_3.JPG

I need to map these fluctuations on a dynamic basis, please have a look at below screenshot `tMap`.tMap.JPG

How can I achieve this? Please help.

Thanks, 

Meet

 

 

Forteen Stars

Re: Dynamic Schema for Mapping in tMap

@meetmahajan,can you check below link. may be useful for your use case

 

https://www.youtube.com/watch?v=bLq8ip6nH5g

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

Re: Dynamic Schema for Mapping in tMap

Hi Meet,

 

     You will have to use the java functions to identify whether the input data is alphabet or number or date and based on the true or false condition, you can move them to target columns.

 

     But my recommendation would to educate your source team that they have to stick to the XML schema definition. For any systems where integration need to happen, the first step is to have an understanding about the interface schema definition and it is missing in this case.

 

      You are now trying to overcome it through additional logic in target systems. Could you please convey this message to them and check with them what is stopping them from transferring data in an agreed format?

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Dynamic Schema for Mapping in tMap

Hi @manodwhb 
Thanks for sharing your valuable knowledge, the video you've suggested is gradually containing different schema in a different file, but I have same input schema and same output schema in different files but the difference is having the attributes contains different values each day.
Please let me know if i've misguided and if you've any queries regarding this.
Thanks,

Meet

Seven Stars

Re: Dynamic Schema for Mapping in tMap

Hi @nikhilthampi

 

Can you please illustrate an example regarding Java function?

Thanks,
Meet

Employee

Re: Dynamic Schema for Mapping in tMap

Hi,

 

    Please refer below links to create the user defined functions for your need. You can embed it in routine and call them as functions from tMap. I would also suggest you to check the numeric and date operation functions of tMap.

StringHandling.IS_ALPHA("abc")
TalendDate.isDate(mydate,"yyyy-MM-dd HH:mm:ss")

 

https://stackoverflow.com/questions/1102891/how-to-check-if-a-string-is-numeric-in-java

 

https://stackoverflow.com/questions/226910/how-to-sanity-check-a-date-in-java

 

https://stackoverflow.com/questions/5238491/check-if-string-contains-only-letters

 

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Dynamic Schema for Mapping in tMap

Hi @nikhilthampi

 

The issue is not to convert the values, it is to map input `Attributes`  to output `Schema` on the basis of `Values of Attribute`.

 
The detailed structure of an implementation @manodwhb 

Suppose for 11th Dec,

C1's value is `10-12-2018`, then it should map with `Date` attribute.

let say, for 12th Dec,

C1's value is `Ahmedabad`, then it should map with `Location` attribute.

Please let me know if you've any queries.
Thanks and Regards,

Meet Mahajan

Employee

Re: Dynamic Schema for Mapping in tMap

Hi Meet,

 

     That is quite straightforward using if else conditions. Following are the high level steps.

 

1) Input is C1, C2, C3

2) Verify each column as per the functions in above link and identify whether it is Alphabet(A), Number(N) or Date (D)

     eg:-     if (StringHandling.IS_ALPHA(input_row.C1) is true

                              {

                                 C1_flag="A";

                               }

                else if (call C1 is date function) is true

                               {

                                C1_flag="D";

                                }

                 else if (call C1 is number function)  is true

                               {

                              C1_flag="N";

                                }

     Similarly you can do the other two columns also and you can perform this action using intermediate Var variables.

 

Now you have the values for example C1_flag="A", C2_flag="N", C3_flag="D"

 

3) In the output section of each variable, assign the values based on flag values.

eg:- For confirmation number, please check using condition checks C1_flag=="N"?C1 : (C2_flag=="N" ? C2: C3)

    This will make sure that the column with only number will be assigned to target column.

 

   Sorry, I do not have enough time to do full coding else I would be more than happy to create the full flow. But I hope you got the logic to do it.

 

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seven Stars

Re: Dynamic Schema for Mapping in tMap

Hi @nikhilthampi

We receive all the `input attributes` as String( Your condition is based on dataType), is there any solution for this type of scenario?

Regards 
Meet

Employee

Re: Dynamic Schema for Mapping in tMap

Hi,

 

    My logic is based on the fact that all the input variables are string.

 

     I have mentioned in earlier posts that you will have to use java functions to verify whether the input string is alphabet or number or date. Based on this verification only, you are setting the flag values. Could you please refer the stackoverflow links to create the appropriate verification functions?

 

     Point to be noted is that at least your source team should agree to a common date format. If the format of date is also not given, you will have to do multiple date format checks in your isdate() function.

 

Warm Regards,

 

Nikhil Thampi

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Highlighted
Seven Stars

Re: Dynamic Schema for Mapping in tMap

Hi @nikhilthampi

 It seems like inappropriate solution what if there are so many strings like
if C-numbering has attributes like Arrival Date, Departure Date, Name, Location, and Address.

Java Code will rectify date as per your solution but what about the date of type(arrival or departure)?
The java code will interpret ALPHANUMERIC, but is it a NAME or ADDRESS or LOCATION?

 

Please let me know if it makes sense.

Thanks 

Meet

Employee

Re: Dynamic Schema for Mapping in tMap

Hi Meet,

 

     You are right since lot of additional computation power will be needed since your XML is not having a proper structure.

 

     I would say you are just seeing the tip of the ice berg. There are lot of finer aspects like understanding first name and last name, standardization of name, address etc. I recommendation always is to create XML flows based on an agreed XSD patterns between source systems and target systems.

 

     In today's world, you can still parse the data whatever may be the format using machine learning models and all those stuff. But the question is how much you are ready to invest in data parsing and how much resources are available for you from system perspective. 

 

      The most easy way is to create an interface document using XSD which is agreed by your source and target systems and allign the flow to those XSDs. Your required computation power will be a fraction of what you are trying to achieve now. 

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads