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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Map files to generic table using reference data

Hi everyone,
I have recently been asked by my company to explore Talend as a way to perform ETL and my job right now is to create a job that imports any type of file, read it with a reference table storing its information (number of fields, separators, etc.) and stores it in a generic table after reading in a reference data table the field in the generic table each field of the original table goes to. If the explanation isn't good enough, you can check the screenshot I posted below (part that requires attention is the one after OnSubjobOk, everything else works).
  0683p000009MD0Z.jpg
I can already successfully import the files, read the information on their separators in REF_FILE_MAPPING, store all the information in a GENERIC_FILE_LOAD table and have the reference data in REF_FIELD_MAPPING with, among other columns, FIELD_NAME (field name of original file) and MAPS_TO (field of the final table GENERIC_FILE that field goes to).
But I'm having troubles in how to do this mapping from a conceptual and also programming level. I tried to do it with tMap but I couldn't figure it out. I also come from MATLAB where I could just access tables with
tablename[][] and I was expecting to do it with, say row6<J>=something</J>

but that doesn't work and I don't know how I can access the tables I import with tOracleInput.
My code on t_JavaFlex_2 is:
Start code:
int NumFields = Integer.parseInt(globalMap.get("NumFields").toString());
int NumRecords = (Integer) globalMap.get("tFileRowCount_1_COUNT");
String[] GenHeader=new String;
Main code:
row6.FILE_NAME = (String)globalMap.get("tFileList_1_CURRENT_FILE");
row6.PROCESSED_DATE_TIME=TalendDate.getCurrentDate();
//row6.LOAD_TIMESTAMP=Integer.parseInt(globalMap.get("LoadTimestamp").toString());

for (int i=2; i<NumFields;i++){
GenHeader="COL" + StringHandling.RIGHT("00"+(i-1), 2); //Generic Columns on both GENERIC_FILE_LOAD AND GENERIC_FILE are called COLXX
};
for (int i=1; i<NumFields; i++){ //Not wanting the Header
      for (int j=2; j<NumFields; j++){ //First two columns are already stored before
           boolean test=true;
           for (int k=1; k<NumFields-1 && test; k++){ //iterating lines of REF_FIELD_MAPPING
                if (row5<K>==GenHeader<J>) //Column 3 of row5 is FIELD_NAME
                    {test=false;};
                row6]=row3<J>; //Column 7 of row5 is MAPS_TO
                };
           };
     };</J>
</J></K>

If this is too confusing, feel free to ask for further information.
Thank you!
Labels (3)
1 Reply
Anonymous
Not applicable
Author

Hi
Take a look d ynamic schema feature which is only available in enterprise subscription product, you might be interesting in it.
Regards
Shong