how to adding rows with conditions
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This table includes the historical item cost with the week of when the cost has been updated.
For example, the cost in WK1 was $10, and this cost remained the same until WK4 when it increased to $15.
item | Cost | Week |
Apple | $ 10.0 | 1 |
Apple | $ 15.0 | 4 |
Apple | $ 12.0 | 7 |
Apple | $ 8.0 | 10 |
I want to make this table with full consecutive weeks as below, so the cost in WK 2 and WK3 should be 10.00 as well.
item | Cost | Week |
Apple | $ 10.0 | 1 |
Apple | $ 10.0 | 2 |
Apple | $ 10.0 | 3 |
Apple | $ 15.0 | 4 |
Apple | $ 15.0 | 5 |
Apple | $ 15.0 | 6 |
Apple | $ 12.0 | 7 |
I have created another table with full yearly weeks from WK1 to WK52... but I can't figure out a way to join these two tables or use a formula to make the cost table be come consecutively.
Also, some data do not star from wk1 or has an ending week as below
Item | Cost | Week |
Peach | 25 | 4 |
Therefore, for the above case, since we are now in WK21, I wish the formula can generate the lines for cost from WK1 - WK21 at $25
As the item cost table update by weekly, I want to make a query keep updating with consecutive week cost.
Thank you
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ll1100000
Does this do what you want to accomplish?
1. Use the Generate Rows Tool to create all the weeks.
2. Use the Append Tool to create all combinations of items and weeks.
3. Join to assign the known values to items and weeks.
4. Sort, the forward fill missing cost values using the Multi-Row Tool.
5. Reverse sort, then forward fill the remaining missing cost values that occur before the first known value (your Peaches example).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Philip Just realized you posted a solution while I am working on it :). Hi5 to you. We both have a same thought process!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Philip
When I download your query and it was running successfully.
Then I copied exact query by using Alteryx 10.4 version since this is the one my company is using now, I got the following error message in the Generate Rows tools
Parse error at char (0); unknown variable "allweek"
Could you please advise what's wrong with it? I just dragged the Generate Rows tools and typed exact what you typed in your query....... because of this error, I even can't run the query to pass the appendent tool.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
But what if you do not want to start with week one and want to cross year boundaries? For example, a one year period beginning with the current week?