Hi all, I have two tables called users and campaign, attached sample .xlsx below. users shows the users created through the campaigns. campaign shows the cost for each running campaign per day. sample_dataset.xlsx (11.5 KB)
I want to find the cost for each user created per day and per campaign. Note that, not every day there is a user created from a campaign.
Hey @ipazin, the screenshot was wrong and that was already late to Edit the topic.
Do you mind if you write your answer and attach the Knime workflow here?
under assumption campaign table is left and user table is right start with left outer join, count number of same campaigns on same date, then perform inner join, divide to get proper cost and do a little bit of cleaning.
If I do $cost$/$Unique count(campaign_id (#1))$ I get wrong results.
i.e. I only need to divide the cost by users for the same campaign_id as the desired output above.
If I got it right your grouping columns are date and id so you should always used them (both in GroupBy node and joins) to get what you need. Also GroupBy should return number to divide cost so you shouldn’t use Unique count but rather Count?