Six Stars

how to get data max of date and max of date -1 from folder using tfilelist

Hi Talend Folks,

 

  • I have files like XYZ_17Jun2017.xlsx, XYZ_18Jun2017.xlsx, XYZ_19Jun2017.xlsx, XYZ_20Jun2017.xlsx.

for example:

  • I need to get max of date(XYZ_20Jun2017.xlsx) file from folder using tfilelist_1 component and max of date - 1 (XYZ_19Jun2017.xlsx) for tfilelist_2 component.
  • I need to create job below like screenshots.

screenshot for example.png

  • It is a routine process
  • Next day automatically i need to get max of date(XYZ_21Jun2017.xlsx) file from folder using tfilelist_1 component and max of date - 1 (XYZ_20Jun2017.xlsx) for tfilelist_2 component.
7 REPLIES
Five Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

Firstly, whenever possible you should use ISO 8601 format dates (YYYY-MM-DD) especially for filenames, as they can be sorted alphabetically which will also be chronologically, making things a lot easier in most situations. With the current format of your filenames, it would be ridiculously messy finding the most recent one(s) based purely on the name.

 

However, in this case we can presumably use the file modified date to get the two most recent files, as follows:

 

tFileList.png

 

tFileProperties.png

 

tJava.png

 

The key is to have the files in the tFileList sorted by modified date, and descending; then it's just a case of recording the filenames for first two files.

 

You may want to use "abs_path" instead of "basename" which I've used in my example, and obviously you should remove the System.out.println lines, which are just there to demonstrate that it's working.

 

Six Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

Thank you for your reply

 

when I ran your query. i am getting error message. 

Error Line: 880
Detail Message: The method containskey(String) is undefined for the type Map<String,Object>

 

if (row6.basename !=null)
{
if(!globalMap.containskey("XYZ_latest"))
{
globalMap.put("XYZ_latest", row6.basename);
System.out.println("Latest:"+row6.basename);
}
else if(!globalMap.containskey("XYZ_previous"))
{
globalMap.put("XYZ_previous", row6.basename);
System.out.println("Previous:"+row6.basename);
}
}

 

Five Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

It's actually Java code, and Java is a case sensitive language. The method is "containsKey" (with a capital "K").

 

I really should have included the code in my post, rather than just a screen grab, so you could copy it.

 

When in a code editor, or any text field which accepts expressions, you can press Ctrl + Space to bring up auto-complete suggestions, which also provide some basic documentation for functions like this.

Six Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

  • Thank you.coding is working fine
  • I got the output date values like 20-7-2017 (max date) and 19-7-2017 (max date of -1)
  • Based on that values i need to get input data form sources folder

 

Five Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

If you change "row1.basename" to "row1.abs_path" in my example to keep things simple, then you can load the Excel files using two tFileInputExcel components, with the "File name/Stream" set to:

 

((String)globalMap.get("filename_latest"))  or ((String)globalMap.get("filename_previous"))

 

These components would be in another sub-job, which would be connected to the first (which gets the most recent two filenames) using an "On Subjob Ok" trigger.

 

In your original example, you'd not need the two tFileList components, and the tFileInputExcel components would use the above as filenames, but otherwise it should just be the same.

Six Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

As you said i have tried that flow but i can't join tFileinputExcel_5 to tmap_4Screenshot (88).png

As you said , can you please make screenshot for me

Five Stars

Re: how to get data max of date and max of date -1 from folder using tfilelist

If you just remove the OnSubjobOk going to tFileInputExcel_5, then you'll be able to join it to tMap_4.