Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Mr_B
Contributor
Contributor

Extract data dynamically and associates column/value

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.

0695b00000KCHOsAAP.png

   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

0695b00000KCHUHAA5.png

Tmap

0695b00000KCHUqAAP.png

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:

0695b00000KCHPqAAP.png

I have tried several things but am lost.

Any help in resolving this issue would be appreciated.

Labels (3)
1 Solution

Accepted Solutions
Anonymous
Not applicable

Hi

You need further processing to get the expected result, see

0695b00000KCP6XAAX.png0695b00000KCP68AAH.png0695b00000KCP9CAAX.png 

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

View solution in original post

2 Replies
Anonymous
Not applicable

Hi

You need further processing to get the expected result, see

0695b00000KCP6XAAX.png0695b00000KCP68AAH.png0695b00000KCP9CAAX.png 

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
Contributor
Contributor
Author

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.