Transpose columns into rows

Five Stars

Transpose columns into rows

Hello,

I have this tMap output and I would like to transpose these four columns into rows.

 

Columns_rows.png

 

I want the new output to be this:

 

Output.PNG

 

Could someone help me?

 

Thank you!

 


Accepted Solutions
Six Stars

Re: Transpose columns into rows

Hi,

 

You can convert the columns into rows using tUnpivotRows. But I am not able to find this component in the Talend latest version.

I have solved the query with own logic you can also try from your end.

The whole logic is:-

29.PNG

Before triggering the job no. of fields and rows should be known. In your case field is 4 and rows no. is 3. Set these value into context. In tJavaRow multiply both context to find total no. of line.

 

30.PNG 

In the first tMap add all the columns to create a single column (VALID_INVALID) separated by ","

 

31.PNG

 

Then use tNormalize to create no. of rows which are separated by a comma.

In the second tMap use sequence generator to create no.  of line and put in a variable. And use the variable to assign column(DQ_RULE).

Condition is:-

Mathematical.MOD(Var.var1,context.field)==1?"DQ_COMP_PROD"Smiley SadMathematical.MOD(Var.var1,context.field)==2?"DQ_FORM_PROD"Smiley SadMathematical.MOD(Var.var1,context.field)==3?"DQ_COMP_CODPROVEEDOR":"DQ_FORM_CODPROVEEDOR"))  

 

32.PNG

 

Add tSorterRow to sort the column(DQ_RULE). And you will get the desired output.

33.PNG

 

If your query is answered, please mark the topic as resolved.

Thanks

View solution in original post


All Replies
Six Stars

Re: Transpose columns into rows

Hi,

 

You can convert the columns into rows using tUnpivotRows. But I am not able to find this component in the Talend latest version.

I have solved the query with own logic you can also try from your end.

The whole logic is:-

29.PNG

Before triggering the job no. of fields and rows should be known. In your case field is 4 and rows no. is 3. Set these value into context. In tJavaRow multiply both context to find total no. of line.

 

30.PNG 

In the first tMap add all the columns to create a single column (VALID_INVALID) separated by ","

 

31.PNG

 

Then use tNormalize to create no. of rows which are separated by a comma.

In the second tMap use sequence generator to create no.  of line and put in a variable. And use the variable to assign column(DQ_RULE).

Condition is:-

Mathematical.MOD(Var.var1,context.field)==1?"DQ_COMP_PROD"Smiley SadMathematical.MOD(Var.var1,context.field)==2?"DQ_FORM_PROD"Smiley SadMathematical.MOD(Var.var1,context.field)==3?"DQ_COMP_CODPROVEEDOR":"DQ_FORM_CODPROVEEDOR"))  

 

32.PNG

 

Add tSorterRow to sort the column(DQ_RULE). And you will get the desired output.

33.PNG

 

If your query is answered, please mark the topic as resolved.

Thanks

View solution in original post

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog