I'm trying to figure out how to implement this. I'm new to Talend and evaluating it for an upcoming project. The project has a bunch of legacy tables that will need to be unpivoted. So, for example, I'd like to start with a table like this: TrxId Name Amt1 Amt2 Amt3 1 Car 10 45 50 2 Truck 30 60 15 And end up with a table like this: TrxId Name AmountType Amount 1 Car Amt1 10 1 Car Amt2 45 1 Car Amt3 50 2 Truck Amt1 30 2 Truck Amt2 60 2 Truck Amt3 15 Note that TrxId and Name are 'passed through' while the Amt1,2,3 columns are pivoted on a new column AmountType. I'm not sure if I'm using the exact terminology here. Any help would be greatly appreciated. Thanks.
If this doesn't exist I'd help make it a component.
The pseudo looks like:
Let the user select the columns to be unpivoted.
Let the user specify a name for the . (AmountType in above example).
Let the user sepcify a name for the . (Amount in the above example).
For Each inputRow
{
For Each ColumnToUnpivot
{
Create a new OutputRow (schema: input - ColumnsToUnpivot + 2 new columns )
Pass values for columns not in ColumnsToUnpivot from inputRow through to outputRow.
Set the value of 'TypeColumn' to ColumnToUnpivot's name.
Set the value of 'ValueColumn' to ColumnToUnpivot's value.
}
}
Can someone help me get from A -> B on this one? Like I said I'm new to Talend but I don't imagine this would be that hard.
thanks shong,
i was looking for this.
i have a post here: 4492
in my case, i have multiple keys,; so i will try your technique and post back.
however, would it not be more future prove to have a component to handle this type of scenario.
i for one, have plenty of flat-files with similar structures.
Thank you for your answer shong. Sorry it took me a few days to get back.
I have a few more questions:
-Can you explain the purpose of file_inputdelimited2?
-Is there anyway to add multiple rows to an output without using an iterate flow? I really want to make this a component and I believe this would be required. Is this just a limitation of TOS?
Thanks again.
Can you explain the purpose of file_inputdelimited2?
Generally, if there is a output component in a job, it must has a input component, so inputdelimited2 is a input component here and it should only contain one row, because we just want to output the assigned value one time for each value of tForEach. You will see the difference and know why if you write two rows or more than in inputdelimited2.
Is there anyway to add multiple rows to an output without using an iterate flow? I really want to make this a component and I believe this would be required. Is this just a limitation of TOS?
No, there isn't a component can do that.
Best regards
I think this will be very useful as a component. I use Informatica quite a bit and this will be almost like the "normalizer" component the Power Center has. Please do consider it. I can put the request in if no one has done it so far.
Thanks.
Sean
Just to throw this out there: SSIS also has a similar transform (called 'unpivot'). Not being able to do this cleaning is making it a bit difficult to migrate to TOS. TOS, in general, is great though. Keep up the good work everyone, Dane
I think TOS is one of the best ETL tools. Obviously, Informatica has been around for a very long time and enterprises pay $100K just for connectors. I use both though I am more familiar with Informatica based on number of years I have been using it.
TOS has a lot of flexibility built in and wealth of connectors. But there are still few things that I feel we need to bring over from Informatica. One is the Normalizer (unpivot). Another is Router that can send rows to multiple targets with very little coding.
Sean
1) We DO have a tNormalize. It's available in the Processing family.
BTW This component is actually generating many rows from one row.
Here is the result that can be easily generated with your input and this component
1|Car|10
1|Car|45
1|Car|50
2|Truck|30
2|Truck|60
2|Truck|15
So this component is probably missing a little option to process your data exactly like you want.
That's why, shong gave you a more complicate example.
2) When you have specific needs, you candevelop your own components.
This is noty very easy but this can be done if you take the time to think about it.
The main requirement to write a component, is to really understand Java. This is not the case of mose ETL users.
3) That's also why we have a
bugtracker where you can ask for new features.
In this place you can directly interfer on Talend's roadmap with your requests.
Maybe in some cases, we don't do it immediately, but in most of the cases, we try to do our best and you see your new feature less than 4 month after in the main product.
If you have urgent need we also have solutions (but I'm sorry this will cost a few money)
Sean, I definetely believe that you will never have such a public place with any proprietary editor. Even when you pay $100K just for the connectors...
Regards,
