One Star

Very Challenging Scenario, Reading Excel and Lookup

Hi Guys,
I have a scenario and hope you can help me with this:
Input:
 REGULATORY_AGENCIES   |   LEAD_AGENCY       <---- this is a Field Header
 10001; 10002                  |   10003; 10001        <---- value
Lookup (in excel)
Field_Header                    |     CODE        |    CODE_DESCRIPTION
REGULATORY_AGENCIES     |     10001       |     Apple
REGULATORY_AGENCIES     |     10002       |     Banana
REGULATORY_AGENCIES     |     10003       |     Carrot 
LEAD_AGENCY                   |     10001       |     Apple Juice
LEAD_AGENCY                   |     10002       |     Banana Juice
LEAD_AGENCY                   |     10003       |     Carrot Juice
Expected Output:
REGULATORY_AGENCIES   |   LEAD_AGENCY                        <---- this is a Field Header
 Apple; Banana                  |   Carrot Juice; Apple Juice        <---- value
In this example, I need my Input to lookup to the lookup table and return its description. I have already created a Routines functions with this that reads a excel file (my lookup table) and return its value. BUT I have only relate CODE and CODE_DESCRIPTION. thats why I'm getting multiple values:
REGULATORY_AGENCIES                                 |   LEAD_AGENCY                                          <---- this is a Field Header
 Apple; Apple Juice; Banana; Banana Juice         |   Carrot; Carrot Juice; Apple; Apple Juice        <---- value
My question is, how can I use or read my HEADER in Input so that I can relate it to the FIELD_HEADER in the lookup table? Here is my code:
public static String splitColumnHandling(String Field)
{
String str = Field;
String ValueHolder="";
String[] myarray = str.split("; ");
String a="";
String b="";

try
{
//Create Workbook instance holding reference to .xlsx file
FileInputStream file = new FileInputStream(new File("D:\\MY PROJECT\\lookup_table.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);

//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//begin loop spliting
for(int i = 0; i < myarray.length; i++)
{
a=myarray.toString();
//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
for(int x = 0; x < sheet.getPhysicalNumberOfRows(); x++)
{
//Declare cell position to be read
Row row = rowIterator.next();
CellReference pos_return_value = new CellReference("D1");
CellReference pos_lookup_codes = new CellReference("C1");
Cell get_return_value = row.getCell(pos_return_value.getCol());
Cell get_lookup_codes = row.getCell(pos_lookup_codes.getCol());

if(a.equals(get_lookup_codes.getStringCellValue()))
{
if(i!=(myarray.length-1)){
ValueHolder=ValueHolder+get_return_value+"; ";
}else{
ValueHolder=ValueHolder+get_return_value+"";
}
}
else if(a.equals("NONE SPECIFIED") )
{
ValueHolder="NONE SPECIFIED";
}
}
b=b+"\n"+a;
}

if (ValueHolder.isEmpty()){
ValueHolder=Field;
}
}catch(Exception e )
{}
return String.valueOf(ValueHolder);
}
}//end

Thank you so much and hope you can help me with it Smiley Very Happy
1 REPLY
Community Manager

Re: Very Challenging Scenario, Reading Excel and Lookup

Hi 
Is the Filed Header a fixed value or it might change for each run? If the Field Header are always a constant, you can convert the input data to the below output with tNormalize component. 

and then, you are able to do join with the lookup table based on Field_Header and Code columns.
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business