Conditionally move a column to new row and duplicate all other columns
Hello, thanks for looking at my request.
My use case is that we're receiving some lender data with multiple pieces of collateral in different fields. A line might look like this:
(First field is loan number. X's are other fields)
10000 x x x Ford Taurus Honda Accord x x
10001 x x x Toyota Camry x x
10002...
Ideally what I would like to have happen is for the collateral pieces to be on their own line with all the other fields remaining constant. For example:
10000 x x x Ford Taurus x x
10000 x x x Honda Accord x x 10001 x x x Toyota Camry x x 10002....
I'm thinking of using the tJavaRow component that has logic that tests if the COLLATERAL2 field is empty or not, but I'm not sure how to proceed from there.
Hi Jean, What is the delimiter in actual data for collateral field as well as other fields? because if actual data looks similar to above data, then it is not possible to distinguish collateral fields with other fields. tNormalize would help in this regard... but to implement this, we need to identify the column where collateral fields are available. Thanks vaibhav
Hi Jean, What is the delimiter in actual data for collateral field as well as other fields? because if actual data looks similar to above data, then it is not possible to distinguish collateral fields with other fields. tNormalize would help in this regard... but to implement this, we need to identify the column where collateral fields are available. Thanks vaibhav
The file is actually fixed width. I was simplifying a row just to help conceptualize it, the real rows would look messy on this site since the line lengths are fairly long. The columns are strictly defined with a schema in the tFileInputPositional component, so moving them around shouldn't be difficult. The X's just represent other fields, I don't think including them is relevant.
Hi, If the file is positional, then you are sure that the collateral fields are enclosed inside specific marker positions. This way you can extract the fields. Now second problem is how you are going to identify two delimited items inside single column. if the space is the delimiter then it will not help... i.e. "Ford Taurus Honda Accord", how they are distinguished... usually in source files, they are delimited by "~" some special chars... If you have this special char which separates the fields inside single column i.e. multivalued column, then you can use tNormalize to make it data as you want. Vaibhav
hi all,
try to use tSplitRow. it could help you.
could have some problem as your schema is not constant. Use dynamic schema if necessary (Enterprise version)
sources
Thanks for the replies so far. It looks like I wasn't clear, the two collaterals are in separate fields, COLLAT1 and COLLAT2. I'll give a line from the actual file. The pipe separator is just added in to help visualize the separation in fields. I'm using a tMap already so if possible I'd like a solution that can be done in tMap.
Input file:
Loan Number | Address | Name | Collateral 1 | Collateral 2 |
014003255|Subway 40927, Inc. |"Name redacted" |2008 hyundai tiburon |2010 mitsubishi outlander |20101115|20121115|....
014001227|441 Industrial, LLC |"Name redacted" |2011 Cadillac Escalade EXT | |20110103|20201121|....
Ideally I would like these to show up as the following:
I duplicated the rows with the tSplitRow component, but I ran into an issue where it duplicates the row even if the collateral 2 field is empty, which means there will be a row with no collateral.
