Very Challenging Scenario, Reading Excel and Lookup

Anonymous — Fri, 26 Aug 2016 02:24:11 GMT

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

Re: Very Challenging Scenario, Reading Excel and Lookup

Anonymous — Wed, 07 Sep 2016 04:26:59 GMT

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

topic Very Challenging Scenario, Reading Excel and Lookup in Talend Studio

Very Challenging Scenario, Reading Excel and Lookup

Re: Very Challenging Scenario, Reading Excel and Lookup