I want to perform a column to row transpose of data using Talend

[Anonymous]

Hello,

I have a task at hand in which I am receiving data in the below format.

Account Type      Dept         Description      DLR1    DLR2    DLR3    DLR4   DLR5      DLR6 .......... DLR 60

Sales                   Parts          SEDAN 1          555     456      432        333      224       322                  222

Sales                   Parts          COUP             455       556     332         233       254      121                  521

Sales                   Parts          SEDAN 2        455       556      332        233       254      121                  521

I need to perform a column to row conversion and want a separate for each dealer. It should look like below

Account Type   Dept              Desc.           DLR              Amount

Sales                Parts           SEDAN1        DLR1               555

Sales                Parts           SEDAN1        DLR2               456

Sales                Parts           SEDAN1        DLR3               432

Sales                Parts           SEDAN1        DLR4               333

Sales                Parts           SEDAN1        DLR5               224

....

....

...

Sales                Parts          SEDAN1          DLR60           222

Sales                Parts          COUP              DLR1              455

Sales                Parts          COUP               DLR2            556

Sales                Parts           COUP             DLR3             455

I am using Talen 6.4.0 version and I tried to download tUnPivotRow component to do the conversion, after reading it on the discussion forum. I followed the steps to get the component added under Windows > Preferences > Talend > Components tab. But it isn't working for me.

Can you please suggest any other options to accomplish this with Talend?

[Anonymous]

Hello,

Do you know how many number of rows you have in your input file? is it going to change over the period?

Best regards

Sabrina

[Anonymous]

Hello,

Thanks for your response.

I have currently 6 rows in the input data source. But there is a future possibility that the number of Descriptions can increase.

Regards,

Monik

[Anonymous]

Hello,

I am still looking for a solution for this using Talend.
The count of rows will be fixed as 6 to begin with.

Any help on this is highly appreciated.

Thank You
Monik

[Anonymous]

Hello,

Please have a look at a  custom code tJavaFlex component, which lets you add Java code to the Start/Main/End code sections of this component itself.

The work flow looks like

tFullInputRow --> tFlowToIterate --> tJavaFlex --> tOutputFileDelimited

// First we split the data on the delimiter of the file, assuming comma separated for now
String[] splitData = ((String)globalMap.get("input")).split("|");

// Initialise the three value strings
String value1 = ""
String value2 = ""
String value3 = ""
for (int i = 0; i < splitData.length; i++) {
    if (i == 1) {
        // id field is the first field
        String id = splitData[i];
    } else if (i == 2) {
        unit field is the second field
        String unit = splitData[i];
    } else if (i == 3) {
        // Don't need to do anything with the count data
    } else if (i % 3 == 1) {
        // value1 fields are 4, 7, 10 etc so modulo 3 == 1
        if (value1.length == 0) {
            value1 + splitData[i];
        } else {
            // if the value field isn't empty (ie. for val1b) then we need to add a delimiter for further processing
            value1 + "|" + splitData[i];
        }
    } else if (i % 3 == 1) {
        if (value2.length == 0) {
            value2 + splitData[i];
        } else {
            value2 + "|" + splitData[i];
        }
    } else if (i % 3 == 3) {
        if (value3.length == 0) {
            value3 + splitData[i];
        } else {
            value3 + "|" + splitData[i];
        }
    }
}

for(int i = 0; i < splitData.length; ++i)

/////// MAIN CODE ///////////////////////
output_row.id = id;
output_row.unit = unit;
output_row.value1 = value1;
output_row.value2 = value2;
output_row.value3 = value3;

Let us know if it is Ok with you.

Best regards

Sabrina