How do I 'unpivot' a table?

Anonymous · ‎2008-10-23

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.

Anonymous · ‎2008-10-29

mhirt,
I hope you did not misunderstand my note as a criticism. I use Informatica because the company I consult with (a Fortune 500 shop), that is the only official tool. For my other clients, I use ToS which I find is very flexible. I think Informatica is a white elephant. In fact asking for $100K for a connector is a joke (I think it was Lotus Dominos connector). Here we can easily write such a connector. So you guys rock.
I did miss the tNormalize component as I was not dealing with such an issue at this time. That is my oversight and given shong's explanation, I thought this was not possible to do. I'll definitely try it out.
I have also requested features using bug tracker and actually gotten them added very fast (thanks to plegall).
I am trying to read up and get a better understanding of Java as that will give me more flexibility with this tool and enable me to write new connectors/transforms. My limited expertise with Java is something of a limiting factor.
Regards,
Sean

Anonymous · ‎2008-10-30

mhirt,
As with psm2000, by no means is my suggestion a criticism. I think TOS is great and the public exchange is fantastic. I agree, I won't be able to get this flexibility and openness with a proprietary system. However, for this project a proprietary system, even with its limitations, might be the best bet.
tNormalize is missing a lot more than a little option to format the data. It doesn't bring the column names with it, which is a key part of an 'unpiviot' component. From where I sit, I'm not sure if this is possible with the current core. The work around provided, is just that, a work around. This project requires a lot of these transformations and that's certainly not an easy/clean way to do it.
My original posts were asking 2 things: is there an existing way to do this? If not, how do I go about building one? I'm a developer and I'd be interested in contributing. I'm really just looking for an 'in theory you can do this' go-ahead before I spend time trying to learn the inner workings of the framework and build this myself.
So, with your understanding, which is far greater than mine, do you think I can use tNormalize as a template for an 'unpivot' component? Or is something going to stop me from finishing the job? Is it possible to bring the column names with it?
From what I understand now, this feature can't be built using the 'component' extensibility point. If this is not the case, could someone let me know, in pseudo, what steps I need to take?
Thanks,
Dane

Anonymous · ‎2008-10-31

Hello Sean and Dane
Don't misunderstand me . I like when the product is criticized. I strongly believe that this is is the goal of an Open Source product.

tNormalize is missing a lot more than a little option to format the data. It doesn't bring the column names with it, which is a key part of an 'unpiviot' component. From where I sit, I'm not sure if this is possible with the current core.

This can be done with the existing core and this is not really difficult to implement.

So, with your understanding, which is far greater than mine, do you think I can use tNormalize as a template for an 'unpivot' component? Or is something going to stop me from finishing the job? Is it possible to bring the column names with it?

Don't believe that I don't want you to have the pleasure to develop a component but I have just put a tUnpivot base on the tNormalize in the https://community.talend.com/t5/Archive/CSV-file-problem/td-p/166946. It's not very difficult but sincerely, it's maybe a little difficult as a first component.
I have only added an option to tNormalize in Advanced Settings.
Please tell me if it works like you want.
Regards,

_AnonymousUser · ‎2009-11-09

Thank you for this post. I was able to bring across my Key Column labels using this example, and avoided the tUnPivot and other pain I had there.
I modified this example as follows:
1. added a duplicate Input Delimited that skipped zero header rows, and has a limit of just 1. In other words, it only reads the header row (to get the column names)
2. added a tJavaRow from #1, doing a global PUT of the column headers. Just like the global PUT of the the Amt values in this example.
3. linked #1 ITERATE to the given example, hooking it into the tFileInputDelimited starting point.
Now I have the column headers flowing through alongside the "amount" values from the cross tab.
4. modify the tJavaRow to PUT a string I will parse later in tMap. For me, this is: globalMap.put("Amt1",globalMap.get("ColHeader1") + ";" + row1.amt1); etc through to as many as you need.
The ForEach is unchanged, essentially now providing a parse-able string (semi-colon delimited for me). Now modify the tMap to parse out the combo-value.
5. modify tMap. For ease of readability, I added a variable to get the value from the ForEach: Var.theValue = (String) globalMap.get(((String) globalMap.get("tForeach_1_CURRENT_VALUE"))). Now modify the mapping to parse out the "left" and "right" pieces of the string using the semicolon delimiter. Left side = "StringHandling.LEFT(Var.theValue,StringHandling.INDEX(Var.theValue,";")) ", right side = "StringHandling.RIGHT(Var.theValue,StringHandling.LEN(Var.theValue) - 1 - StringHandling.INDEX(Var.theValue,";")) ". I stored the "left" side into one of the output columns, and stored the "right" side into the other output column.
There you go. The column header labels are 'repeated' into a column. You can modify this as you need, to have as many extra columns headers you like. Just pull them out as per steps 1-3, and then concat them into a parse-able string as per step 4. Pull them back out again as per step 5.
I hope this helps.
--Eric

Anonymous · ‎2011-08-31

Hi,
I have a similar requirement but with a little twist. I have all my metrics coming in one excel sheet in 3 different tables, see the first half of the image attached.
I wish to bring the Months and Years down, and move my 3 Metrics like DSO, etc in the columns, see second half of the image attached.
Kindly suggest how could i achieve the same.
Also the columns in the source i.e months and years would change once in a year when a new year's data would be added, and the old year's data would be removed keeping only 5 years of data at a time.
Your help would be really appreciated.

Thanks in advance,
Diwakar

alevy · ‎2011-08-31

Your screenprint seems a bit dodgy: totals don't add up; the numbers don't align between the input and output; you refer to 5 years' data but there are 5 months...
I've assumed that's just because it's not a true example. The following is designed for 5 columns of values and 3 metrics but can be extended for more. There are other assumptions about sorting etc, which can be tweaked as needed. I've avoided tUnpivotRow as I prefer to only use "official" components, even though that might make the job design simpler.
tJavaRow_3

output_row.Metric = input_row.Metric;
output_row.Country = input_row.Country;
output_row.Value1 = input_row.Value1;
output_row.Value2 = input_row.Value2;
output_row.Value3 = input_row.Value3;
output_row.Value4 = input_row.Value4;
output_row.Value5 = input_row.Value5;
if (tos_count_tFileInputExcel_1<3)
	globalMap.put(tos_count_tFileInputExcel_1==1?"Years":"Months",new String[] {input_row.Value1,input_row.Value2,input_row.Value3,input_row.Value4,input_row.Value5});

tFilterRow: Country Not Equal To "Country" And Country Not Equal To "Total"
tJavaRow_4

String[] Years  = (String[])globalMap.get("Years");
String[] Months = (String[])globalMap.get("Months");
output_row.Metric = input_row.Metric;
output_row.State = input_row.Country;
output_row.Values =  Years+","+Months+","+input_row.Value1+";"
					+Years+","+Months+","+input_row.Value2+";"
					+Years+","+Months+","+input_row.Value3+";"
					+Years+","+Months+","+input_row.Value4+";"
					+Years+","+Months+","+input_row.Value5+";";

tNormalize_1: Values separated by ";"
tExtractDelimitedFields_1: Values separated by ","

Anonymous · ‎2015-06-01

Hello
Here comes a Java scenario, please see the screenshots.
Input file:

TrxId;Name;Amt1;Amt2;Amt3
1;Car;10;45;50
2;Truck;30;60;15
3;Shong;10;20;30

Result:
Starting job topic_4500 at 11:14 24/10/2008.
1|Car|Amt1|10
1|Car|Amt2|45
1|Car|Amt3|50
2|Truck|Amt1|30
2|Truck|Amt2|60
2|Truck|Amt3|15
3|Shong|Amt1|10
3|Shong|Amt2|20
3|Shong|Amt3|30
Job topic_4500 ended at 11:14 24/10/2008.

Feel free to post your question!
Best regards
shong

Hi shong,
can you explain your job?
Got the same problem:
ID;Column1,Column2,Column3;...;Column99
1;25;28;54;...;89
2;85;65;89;...;78

expected output:
ID;Column1
1;25
1;28
1;54
...
1;89
2;85
2;65
2;89
...
2;78

kmrprvn · ‎2017-12-29

screenshots missing. can someone help me on this?? thanks in Advance

Anonymous · ‎2018-01-02

Hello,

Please have a look at this article about:https://community.talend.com/t5/Migration-Configuration-and/Converting-columns-to-rows/ta-p/21658/ju....

Best regards

Sabrina

Anonymous · ‎2018-09-17

Hi Shong,

i am facing the issue , I need to unpivot the data. i ahve referred to your answer but could not find the screenshots.
Could you please share the screenshots once again.

Thanks & Regards,
Har**bleep**ha.

Other

Talend Data Integration