Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

Very Challenging Scenario, Reading Excel and Lookup

Highlighted
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
Highlighted
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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog