I found two problems when I start running the second loop of the data which i don’t know how to correct in the workflow:
Excel files referring to previous link:
Say on 1/1/2019, i have raw data file of tab sheet “Original File Received in wk1” with week number in row 2 from wk51 to wk5.
on 7/1/2019, i have raw data file of tab sheet “Original File Received in wk2” with week number in row 2 from wk52 to wk6.
I load the two files/tab sheets in the designed workflow and worked perfectly well.
However,
Say on 14/1/2019, i receive new raw data file with the same format but only indicate week in row 2 from wk1 to wk7.
When I load the 7/1/2019 file and 14/1/2019 file in the workflow (because i need to get the most update data therefore i need to load 14/1/2019) file.
7/1/2019 and 14/1/2019 respectively for the first two nodes.
then 7/1/2019 and 14/1/2019 again for the 3rd and 4th nodes. Problem happen: Request Date and week don’t match. Week 52 = Request Date 2018-12-17 Week1 = Request Date 2019-12-24
etc
Basically, this is a rolling practice, each week i will receive a new raw data file with updated 7 weeks, for weeks that is not available in the new raw data file any more, i need to keep the data in the output file, for the weeks that are mentioned in the new raw data file, i will take the most update data from the new raw data file.
on 7/1/2019, i have raw data file of tab sheet “Original File Received in wk2” with week number in row 2 from wk52 to wk6.
on 14/1/2019, i have raw data file with the same format with week number in row 2 from wk1 to wk7. Problem happen: If there is some new material number that cannot be found in file 7/1/2019 but is on 14/1/2019, then the material will not appear in output file, but in fact we need those new data in output file.
Problem (1) the unmatch between week number and request date is solved.
But Problem (2) is not solved. attached please find a sample excel file which may help to work on the workflow. in tab sheet “original File received in wk2”, i have added two more rows with material 99999 and 88888, in current knime workflow design, the output file cannot show these two rows because it did not appear in tab sheet “original file received wk1”.
But i will need these new rows/materials be kept in the output file.
(3) Another issue i found is other than keeping tab sheet “original file received in wk1” and “original file received in wk2” in the output file, for the data i received in the past i will still need to keep in the output file. for example, i created tab sheet “original file received in wk-1”, wk52 indicated in row2 does not show in “original file received in wk1” and “original file received wk2” anymore, but i still need that in the output file. I was wondering may be i should concatenate the previous week output file with this week output file, but how can i replace the duplicate week following this week output file most update data?
(4) On the other hand, when using the attached new sample excel file, after running knime workflow, you will find that the GR (SU) in output file of wk4 actually is referring to wk5 GR (SU) in tab sheet “original file received in wk2”. If the excel file is showing “original file received in wk2” week number from 1-7, then the data mates. But if the week number changes from 1-7 to 2-8 in “original file received in wk2”, the output file of knime GR (SU) and week relationship is different with the original file.
