Conversion

[Y2J]

I have been trying to solve this given problem  of 2 columns, code and items, by transposing the items column but in a hierarchical manner (picture attached). I have tried using denormalize and the pivot to column delimiters but nothing seems to work. Is there any method of solving this without the use of java components? If not, how would you approach solving it using the java components? 

[TRF · Accepted solution]

Connect a tJavaRow with the following code after your tFileInputExcel:

// Remove trailing 0s from inpout code
String code = input_row.code.replaceAll("0*$", "");
// Length code gives the level
int level = code.length();
if(level == 0) level++;
// Store item value into the global variable assigned for the current level
globalMap.put("l" + level, input_row.item);

// Global variables from level to 10 are emptied
for(int i = ++level;  i <= 10; i++)
	globalMap.put("l" + i, "");

// Fill output fields
output_row.code = input_row.code; 
output_row.level1 = (String)globalMap.get("l1");
output_row.level2 = (String)globalMap.get("l2");
output_row.level3 = (String)globalMap.get("l3");
output_row.level4 = (String)globalMap.get("l4");
output_row.level5 = (String)globalMap.get("l5");
output_row.level6 = (String)globalMap.get("l6");
output_row.level7 = (String)globalMap.get("l7");
output_row.level8 = (String)globalMap.get("l8");
output_row.level9 = (String)globalMap.get("l9");
output_row.level10 = (String)globalMap.get("l10");

[Y2J]

Hey,

This is still giving me an error! It is showing ' level1 can not be resolved or is not a field'.

Please advice on changes that can be made to make it work. The input and output rows were changed and adjusted according to my project, so that should not be the reason.

[TRF]

You need to defined the tJavaRow schema like this:

[Y2J]

Thanks a lot ,it's working now.