Select latest valid file (Iterative Macro)

Hi All,

I have to select the latest file. In the file the headers are in the 5th row but some time there are no headers at all. So in that case we need to select the 2nd latest file and check if headers are in it or not and keep repeating until I get a valid file. Once I get the valid file will process further and no need to check further.

So I am sorting the files in such way that latest file is at the top and using an iterative macro to check each file one by one. and the put a filter based on the condition to check but by this I am getting only 1 row in my output . I need to put in a new column with a flag say true or 1 in each row of the dataset when we get a valid file so that all dataset is picked once we get a valid file. I tried but did not had any luck.

Could anyone please suggest what we could do here?

I there is another solution design available, that would also be great.

Iterative Macro

Accepted answers

Shubh07kr

Hi @apathetichell, @aatalai, Thanks for your solution. Although I got my requirements fulfilled without macro.

I am reading the 1st row of last 5 latest files also taking the file name and using regex to check the first column name. For all the rows matched, I am sorting sorting it and then pick the first row and get the file name. Then use dynamic input to read the data.

All comments

caltang

Well if you provide some sample data, it will be very helpful to illustrate your point.

aatalai

you can use the directory tool then sort on one of the date time fileds and then use that to open the specfic file

apathetichell

@Shubh07kr --- agreeing with @aatalai here -- I probably wouldn't use an iterative macro here. I would:

1) select the nth most recent files (say 5)

2) send them into a batch macro which retrieves the first row (including headers) of each file and the file name.

3) send out to my main workflow.

4) --- branch out from 1) to send to a different batch macro.

5) pull out all data from most recent file.

6) parse headers from first macro to identify the headers.

7) add headers via dynamic rename to data source from 5.

Shubh07kr

Hi @apathetichell, @aatalai, Thanks for your solution. Although I got my requirements fulfilled without macro.

apathetichell

@Shubh07kr hey --- so word of advice --> do this as a batch macro now vs later. Sooner or later you will have a phantom field (field in one file, but not in the others, or a different type in one file than the others) which will lead to a schema conflict. This will crash dynamic input and your workflow. If you use a batch macro you've worked around this issue.

Yogesh1795

it would help if you attach some pictures. I would use directory and dynamic Input tool, the dynamic input tool ask for reference, if your input file from directory match the reference it get the records of that file only.

Shubh07kr

@apathetichell In my case schema won't change user is ok with the current solution. Although thankyou for your advice, will keep in mind this next time if have similar kind of request. 😊

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703