Hi, I’m trying to solve a problem with data that is diffusely scattered. I could see that the dates that exist in the table are the only “pattern” that I can use, but I honestly don’t know how. I am attaching an example of what the table looks like and how I would like it to look. Thank you very much
In general you can achieve this using a Joiner Node.
I take though that you will have multiple transactions that occur on the same day? i.e. In each table each date is not unique?
In that case it might be worthwhile to “unpivot” the ACC1, ACC2 etc. columns and then join based on date and Acc… Can you provide an anonymised sample data set?
1 Like
Yes, i hope this works for you
example.xlsx (9.3 KB)
Btw, im trying to use lag column to make it work. but i dont know if is the right node
ok thanks for the example. I think I misunderstood what you were trying to do.
I’ve created a prototype:
accanddatejoining.knwf (98.8 KB)
Overview:
Explanations:
Column Expressions is used to turn any “-” in the date column into a missing value. I also “clean” all the “ACC” cols so that the data can be converted into numbers.
Missing Value: I use this to replace any missing value that I created on purpose in the previous node with the last previous value that was not missing - this assumes that if there is a “-” the date is the same as in the last row above that had a date
String to Number: Safety check to ensure ACC cols are all converted
Group by. I have to options: One groups by Date only and aggregates movement using “Concatenate” and sums up the ACC columns. This to me looks weird that’s why I tried again grouping on date and COMP column…
So this I think achieves what you were after, but think you need to check if the results now make sense…
Group on Date:
Group on Date and Comp:
3 Likes
Hi martin,sorry for the delayed reply. Your WF works pretty well for me, but i have to use a different aggregation method. So in the end it was helpfull.
tnks
2 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.