How to Create new scaffold data from multiple data source

[smurakam0201]

Hi Community I have similar format two data source and want to append the data. But at the same time, I want to create all possible scaffold "cells" in new data set. Attaching excel file to show what i want. Thank you for your help in advance. Shin

[NicoleJohnson · Accepted solution]

There's probably a simpler method... but try the attached? Seemed to work...

Basically use Summarize tool to group by all possibilities for the different fields (Process, Version, Period)... union them together between datasource 1 & 2... then Append all the fields back together to create your list of all possible Version-Process-Period combos... then join back the info from the original tables to populate the amounts.

Let us know if that helps :)

NJ

[smurakam0201]

Hi Nicole

Thank you for attaching the sample.  It's understandable to use this step by step process.

Definitely it helped, not only this specific, but also as learning opportunity.

One more question, because I'm newbie, is that is it possible to keep "Null" for non-existing cells instead of "0" ?

Thanks,

Shin

[NicoleJohnson]

Yes, you could modify the formula that combines the amounts as follows to leave the nulls in there (basically says if those fields are null, keep it null, otherwise add the two together):

if isnull([Amount]) && isnull([Input_#3_Amount]) then null() else tonumber([Amount])+tonumber([Input_#3_Amount]) endif

:)

Happy to help! Good luck, and keep those questions coming - it's the best way to learn (and the best way for others in the community like me to keep from getting rusty!!)

NJ

[DanS]

Awesome Nicole!

I took the time to solve this too and did it a little different. (left nulls in)

Posting workflow if anyone's curious.

[smurakam0201]

Thank you Nicole.

I just start learning, good lesson.

Have a good weekend.

Shiin

[smurakam0201]

Dan

Thank you for providing the different solution.

It is always good to know different approach.

I just start learning Alteryx and all of these inputs are really helpful.

Have a good weekend.

Thanks,

Shin