Unique function

[Feras95p]

Dear all,

I have 5 excel files which contains list of orders, in each order I have the code of the order and status of the order as well.

Status (1) means the order is delivered.

Status (2) means the order is postponed.

Status (3) means the order is canceled 

Status (4) means the customer didn't answer.

Now if the order's status is 2 or 4 it will be rewritten in the next file to re-contact with the customer and maybe the status will changes from 2 to 1 or 3 or 4 or stays 2 it depends of the customer's respond.

These 5 files are the files of the orders from Wednesday to Sunday. 

Now what I want is to have all these files in one file and remove the duplicated orders because as I explained the order in the file of Sunday which its status 2 or 4 will be rewritten in the file of Monday with the same code and its status may be changed to 1 or 3 or others so in this way how can I use the unique tool and make sure that it takes the latest status.

Note that, as shown in the picture below each color represent one order. Follow the same for all files.

 

[BrandonB]

If the files have the date within the name, it may be best to "Output File Name as Field" and parse the dates out. Then, you can sort by the date descending so the latest orders are up top, and finally do a unique tool on the code field. This way if there are any duplicates, they will be filtered out because they will be further down the data set after the sort occurs. 

[Feras95p]

Hi @BrandonB,

Unfortunately, the dates are not accurate because if the status of a Wednesday's order is 2 at the date 20-05-2020 then it will be written again in the Thursday file with the same date and different status "depends on the customer's respond".

Any other idea?

[BrandonB]

I am referring to the date of the actual file creation, not the date within the data. Alternatively, you could also use a directory tool to determine the file creation date if the date the file was written isn't in the name of the file. You would use this date to determine which data is from the latest files which would take precedent. 

[Feras95p]

Hi @BrandonB ,

Thanks for being up with me, unfortunately the create date not in order with the days as shown 

Can I use the last access time? 

[BrandonB]

If you have the file names like that, you can actually bring out that data by using option number 4 where you output the file name as field. Then you can use that value to sort. 

 

[BrandonB · Accepted solution]

I attached a screenshot below of what this can look like and included an example workflow. Using the file name, I parsed out the date using the date time tool. Then I sorted it descending. Finally, I used a unique tool on my customer ID. In your case it would be the code. 

 

[Feras95p]

I see, in my case I have to use 5 inputs and use a union tool before the date time tool to be able to use all files, correct?

[BrandonB]

That is correct. If you are using 5 input tools you will want them all configured to have file name as field and union them together prior to the next steps

[kelsey_kincaid]

If all the files are in the same folder you can always formulate your input tool with a wild card. This article has a fairly good rundown of how that works: https://www.thedataschool.co.uk/borja-leiva/4840/