Extract data dynamically and associates column/value

[Mr_B]

Hi,

This is my first post and I am asking for your help.

I've been looking for a solution for several days by looking on the web or looking for similar posts but I can't find any solutions.

I have a csv file with a header consisting of 3 columns and 3 rows of associated data.

Column1; Column2; Column3

value_A; value_B; value_C

value_D; value_E; value_F

value_G; value_H; value_I

I am trying to have the following result in output in a file which groups together on two columns: the name of each column and its value

Column1; value_A

Column2; value_B

Column3; value_C

Column1; value_D

Column2; value_E

Column3; value_F

Column1; value_G

Column2; value_H

Column3; value_I

I cannot specify the name of the input columns so I dynamically read my input file and manage to extract the columns and values.

I used the following link to achieve this: Scenario 2: Extracting the contents of a dynamic column via tJavaRow

I am stuck building my output file with the expected result.

I adapted the code to achieve my ends but to no avail.

   Dynamic columns = input_row.line;

   String name_column = "";

   String value_column = "";

   for (int i = 0; i < columns.getColumnCount(); i++) 

   {  

     DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

    name_column += columnMetadata.getName().replace("_"," ");

    value_column += columns.getColumnValue(i);

   }

   output_row.column_name = name_column;

   output_row.column_value = value_column;

TFilterColumn

Tmap

The problem is that in output, I get my two columns, but I have the column names on one side and the values on the other:

I have tried several things but am lost.

Any help in resolving this issue would be appreciated.

[Anonymous · Accepted solution]

Hi

You need further processing to get the expected result, see

 

tJavaRow1:

Dynamic columns = input_row.line;

  String name_column = "";

  String value_column = "";

  for (int i = 0; i < columns.getColumnCount(); i++) 

  {  

   DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

  if(name_column.equals("")){

  name_column=columnMetadata.getName();

  }else{

  name_column= name_column+";"+ columnMetadata.getName();

  }

   if(value_column.equals("")){

  value_column=(String)columns.getColumnValue(i);

  }else{

  value_column= value_column+";"+ columns.getColumnValue(i);

  }

  }

  output_row.column_name = name_column;

  output_row.column_value = value_column;

tJavaRow2:

//Code generated according to input schema and output schema

output_row.sequence_id1=Numeric.sequence("s1", 1, 1);

output_row.column_name = input_row.column_name;

tJavaRow3:

//Code generated according to input schema and output schema

output_row.sequence_id2 = Numeric.sequence("s2", 1, 1);

output_row.column_value = input_row.column_value;

For detailed information, please import the job items into your studio.

Hope it helps!

Regards

Shong

[Mr_B]

Hi Shong,

Thank you very much for this feedback and for these explanations.

I better understand the logic of reasoning to arrive at my final result.

I should have tried to separate the spots a little more, like you associating a sequence to each content to bring them together in a tMap.

Thank you again for helping.