topic Re: One to Many mapping in Talend Studio

One to Many mapping

_AnonymousUser — Sat, 16 Nov 2024 14:33:10 GMT

I would like to process the following input file

Company = XYZ
Date = 12/4/2007
District = 5

A=23 B=23
C=34 D=23
E=34

F1=23,F2=23
F1=24,F2=33
F1=25,F2=34

A=23 B=23
C=34 D=23
E=34

F1=23,F2=23
F1=24,F2=33
F1=25,F2=34
My problem is that I don't know how to map the subrecords to my schema. I though of using a FileInputRegex to process this file. How will this be associated with the schema.
Any assistance is greatly appreciated.
Regards
Alex

Re: One to Many mapping

Anonymous — Wed, 19 Sep 2007 10:17:56 GMT

Hi Alex
I'm not clear about your question. Is the Header repeted several times in the files or is there only one header per file? what is the output expected? Can you provide a screenshot about your related schema information?
Best regards
shong

Re: One to Many mapping

_AnonymousUser — Wed, 19 Sep 2007 10:41:41 GMT

My problem is that I don't know how to construct a schema for my model. My model consist of a record (row) with a subrecord. Each record consist of:
A=23 B=23
C=34 D=23
E=34
but also has subrecords :
F1=23,F2=23
F1=24,F2=33
F1=25,F2=34
I suppose I could expand / flatten this to a schema like this :
A=23, B=23, C=34, D=23, E=34, F1=23, F2=23
A=23, B=23, C=34, D=23, E=34, F1=24, F2=33
A=23, B=23, C=34, D=23, E=34, F1=25, F2=34
This would however mean that the first 4 attributes are duplicated and it would make it more difficult to identify with F sub-records belong to which A,B,C and D record. If this is the only option, is there a friendly and easy method of tranforming the input into the flattened format.

Re: One to Many mapping

Anonymous — Wed, 19 Sep 2007 11:33:28 GMT

Hi Alex,
This is an interesting case, not too easy to deal with though.
Let say that "A=23" and "B=23" are properties of the record and "F1=23","F2=23" are properties of the subrecords.
Can you let us know:
1/ is the number of properties per record limited? In your example, there are 5 properties. (A, B, C,D,E), is this always the case?
2/ is the number of subrecords per record limited ? and always identical? In your example, there are 3 subrecords.
3/ is the number of properties per subrecord limited? In your example, there are 2 properties per record (F1 and F2).
It could be helpful, if you could provide us with two different records at least, so we can see where and how the changes are made...

Cheers,
Elisa

Re: One to Many mapping

_AnonymousUser — Wed, 19 Sep 2007 11:43:42 GMT

1.) The fields in the main records are typically the same for each main record
2.) The fields in the subrecord are the same, but the number of subrecords for each main record differs
3.) See 2
A better example would be the relationship between a person and their phone numbers. Once could envisage a input file like this
Name=John, Surname=Smith, Age= 56
Phone=12345,Type=Home
Phone=23456,Type=Office
Phone=64332,Type=Mobile
Name=Jennifer, Surname=Smith, Age= 23
Phone=12345,Type=Home
Phone=543421,Type=Office
Phone=234563,Type=OfficeFax
Phone=123423,Type=Mobile
Thanks for your help
Regards
alex

Re: One to Many mapping

Anonymous — Wed, 19 Sep 2007 13:27:50 GMT

First of all, let me say that TOS current design does not very well fit with this kind of input file.
Here is the less ugly way to do it I've found.
My input file looks a lot like yours (but with distinct record values so that we better see the origin of a subrecord:


Company = XYZ
Date = 12/4/2007
District = 5

A=23     B=23
C=34     D=23
E=34

F1=23,F2=23
F1=24,F2=33
F1=25,F2=34

A=9      B=42
C=98     D=56
E=12

F1=23,F2=23
F1=24,F2=33
F1=25,F2=34
F1=26,F2=35

My tPerlRow_1 code is:

while ($input_row =~ m{()=(\d+)}g) {
    $_globals{'current'.$1} = $2;
}

And my output is:

Starting job topic1337 at 14:24 19/09/2007.
.----------------------------------.
|             tLogRow_2            |
+----+----+----+----+----+----+----+
| a  | b  | c  | d  | e  | f1 | f2 |
+----+----+----+----+----+----+----+
| 23 | 23 | 34 | 23 | 34 | 23 | 23 |
| 23 | 23 | 34 | 23 | 34 | 24 | 33 |
| 23 | 23 | 34 | 23 | 34 | 25 | 34 |
|  9 | 42 | 98 | 56 | 12 | 23 | 23 |
|  9 | 42 | 98 | 56 | 12 | 24 | 33 |
|  9 | 42 | 98 | 56 | 12 | 25 | 34 |
|  9 | 42 | 98 | 56 | 12 | 26 | 35 |
'----+----+----+----+----+----+----'
Job topic1337 ended at 14:24 19/09/2007.

The trick is to detect in the tMap whether the current line is a record line or a subrecord line. If it is a record line, we create/update the related global variables, else we use the current value of the global variable.

Re: One to Many mapping

Anonymous — Wed, 19 Sep 2007 13:32:22 GMT

And also... look with attention the "record" output in the tMap, the "Enable/disable output reject" button (the orange arrow) is checked.