I have one global file with a lot of lines containing inputs to be processed by a team. Every line is & correction.
To check the processing I receive many project files (1 by project) that should contain a line with the input correction.
So I expect the individual project files to contain the input corrected line in the global file, among many other lines.
I would like to see if the line in the global file exists in the project file with following fields from the global file being identical: TS PERIOD / FMNO / PROJECT / HOURS /PER DIEM
with the following fields from the individual project files:
Project number/Fmno/Hours/WIP per diem/Time-sheet period
More specifically, I want to see the lines that exist in global file but do not appear with the fields above with identical values. This basically means it was not processed correctly.
On top of this, when TS PERIOD, FMNO, PROJECT and PER DIEM are the same in the global file, I would expect the sum of hours to be found back in the individual project file. If hours are 10 and -5 in the global file I would expect hours 5 in the project file. To see that this was not processed correctly, I would need to see that the line in the project file containing 5 not exists so that i know it has not been processe correctly.
I gave it a try but sth went wrong along the way.
Basically I put everything together in 1 file and then added columns with filenames if the values are the same.
Not the best way probably, but if it would work...
Happy to hear any suggestions.
example input files attached (1 global file and 2 project files)
Solved! Go to Solution.
Thank you!
It seems to work
the only problem is that i have around 60 project files so i need to upload them at once, and not one by one.
Pointing at the file and filling *.xlsx loads all the files in a directory but how would this alter the alteryx flow?
Pls note i have version 11.0 but i managed to downgrade the file from 11.7 to 11.0
thanks!
Uploading the solution with the option to read multiple project files. Below are summary of changes made.
1. Place project files and global file in two different folder/directory.
2. Use Input data tool for reading the project files instead of directory tool and configure it with below options.
Connect a File or Database: <filepath>\*.xlsx
Table or Query: 'Time Details$'
Search SubDirs: To be checked
Output file name as field: File name Only.
3. Configure two output tool to segregate correctly processed and incorrectly processed from project files.
thank you for the effort and the flow.
Unfortunately it is not providing the solution I need.
I have the impression this scans the project file and checks line by line if this appears in the adjustment file (output tab).
In fact I need to the check in the other direction: going line by line in the output tab and checking if this line exists in the project file. The project file will by default contain many many more lines but that does not matter. The only thing I want to see if the line in the output file exists in the project file.
How this is represented is the same for me, but essentially it is a ok or nok for every line in the output file.
Dont know if this is clear, but would be very happy to have a solution for this one.
regards
Kim
Maybe my requirements are not clear.
hello
thank you very much. this is what i need.
the last extra requirement is to sum the hours in the global file if FMNO, timesheet period, project number and per diem is the same.
meaning: in the global file having two lines
TS period 1803 FMNO 123456 per diem 450 hours 5
TS period 1803 FMNO 123456 per diem 450 hours 15
I would expect correct processing in the project file that hours are summed
TS period 1803 FMNO 123456 per diem 450 hours 20
If I have this, that would be perfect. But the current solution is already a very big step forward.
thanks again!
kim
uploading the workflow which will give you output hours summed...
I am giving two output option.
Output option 1: output file will have column called "correct/incorrect" which will tell whether a line is correctly processed or not.
Output option 2: output file will have two sheets, namely correct and incorrect
Let me know in case you require any further in this regard...
hello
again thanks for the work done, it worked for me as i did some tests. I only change the initial check on null value to the project code field.
I will now send it to other person for testing. Should have an anwser tomorrow or wednesday so that i can accept the solution
again many thanks!!!
Can you mark the workflow which helped you to resolve your problem as accepted solution ?