How to create dynamic filename from a tMap output field?

[Anonymous]

Hi I have a simple Talend job like below:

 

Here I used tLogRow to display the tMap output, but what I really need is to write the data to a file 

( with the tFileOutputDelimited component).

I want to write the left 4 columns into a file and use the most right colum ("N_WEEK") in my filename.

How should I refer the N_WEEK in my File Name within the tFileOutputDelimited component? So far I always got the value "null" with all means I tried.

Thanks

[Anonymous]

Hi,

    If you would like to write each row to individual file, the best idea will be to use tFlowtoIterate component after tMap. Once its added, you can process one row at a time and can fetch individual columns by using Control+space and selecting the columns listed under tFlowtoIterate. You can do it within the tfileoutputdelimited component's file name also.

  Now, if your plan is to write the file name with first row's column, my suggestion will be to pass the data to a tjavarow where you can transfer the data of N_WEEK column of first row to a context variable. Please note that it would be a good idea to add a sequence in tmap output so that you can easily identify first row. You can use the context variable, later in your flow by adding it in file output component.

  Could you please try one of the above options based on your use case and let me know the results. If you are stuck somewhere with some error, please share the job and component screenshot, so that we can fix them quickly.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

nikhilthampi,

Thank you for your reply. Let me explain what I want to accomplish.

Let me start with my job that is already doing what I want:

  

Below is the file for the tFileInputExcel. The business side provided the file and was asking for the top 100 selling UPCs for each week.

 

What I did in my job is : read each row from the excel file, row by row, and create the global variables within tFixedFlowInput component -

 

Then within tDBInput, I query the database to get the top 100 UPCs for each requested week -

 

And the last, I wrote each top 100 UPCs into a file with the week info in the filename -

  

And here is what I got in my local folder : 8 files, each has 100 rows -

 

But I also want to accomplish it using another job design, something like below (not working yet) :

I pass the week info from excel into tMap and create global var within it -

In the tDBInput component, I use the global var "weekStart" and "weekEnd" to fetch top 100 UPCs for each week, I hardcode a FORCE_MATCH field ( 1 is the value) and use that to do the inner join. 

And here is the output from tMap -

 

I want to write the top 4 field values into a csv file and I want to have the value from N_WEEK for that filename. When I run the job, I see the week info seems to be read row by row and tDBInput fetches 100 rows each time, but all 800 rows (8x100=800) go to one file and the file name is : top100file_null.csv.

I tried different ways to get the "Week" info for the filename: 

StringHandling.CHANGE((String)globalMap.get("row1.Week"),"/","") 

topN_selling_UPCs.N_WEEK

...

Nothing worked. Please help!

[Anonymous]

Hi
It does not work this job design, because the lookup table always works before the main table, so the global variables in the query will null.
Regards
Shong

[Anonymous · Accepted solution]

Hi,

@shong is absolutely correct as you can observe that for any flow, the lookup data will be fetched first before invoking the main flow. In your flow, you are trying to pass the global variable from main data to lookup flow. So it is not getting the right values and it is the root cause why you are not getting the desired output.

Your first approach is the right method as you are picking the data for each input row in an iterative fashion. This method is scalable since it does not have any dependency with rowsize.

You need to note that you will have to perform top 100 selling UPCs by running the query within database. So another method is to pass the input data file to a temp table in database and do the aggregation for all the input records in one go and store the result set in another temp table. Please note that in this temp table, you will have to capture the input row identifiers also so that you can do the match. In the next sub job, you can extract the data for one customer at a time in an iterative fashion. 

But you need to note that even though the aggregation was executed only once in this method, you will still have to do iterative scans on the temp table to fetch the result set to output files.

So I would say, your first approach is good one and please proceed with that method.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂