community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Multiplication Union data

Meteoroid

Hi all,

 

This issue have been keeping me up over the weekend. Below table shows the result of my union data: 

 

I would like to create a new column where it will multiply D1 No. of Days Week 1 with 1st Week Data + D1 No. of Days Week 2 with 2nd Week Data + 

 

D1 No. of Days Week 3 with 3rd Week Data : (4*111.97 + 6*111.88 + 0*111.88) 

 

How could I create a formula that is dynamic where it will skip the Null data under 1st week Data and goes to 111.97 and multiply it with 4 under D1 No. Of Days  ? 

 

Many thanks in advance for your help ! 

 

MonthWeel before first 10 daysWeek before 20 daysWeek before 30 daysWeek before EOMD1 No. of Days Week 1D1 No. of Days Week 2D1 No Of Days Week 31st Week Data2nd Week Data3rd Week Data 4th Week Data5th Week Data6th WeekData 
5/1/20191245460[Null][Null][Null][Null][Null][Null]
5/1/2019[Null][Null][Null][Null][Null][Null][Null]111.97[Null][Null][Null][Null][Null]
5/1/2019[Null][Null][Null][Null][Null][Null][Null][Null]111.88[Null][Null][Null][Null]
5/1/2019[Null][Null][Null][Null][Null][Null][Null][Null][Null]111.88[Null][Null][Null]
5/1/2019[Null][Null][Null][Null][Null][Null][Null][Null][Null][Null]110.13[Null][Null]
5/1/2019[Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null]109.68[Null]
5/1/2019[Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null]110.15
Alteryx
Alteryx

@JLEE038 

 

It would be good to see the shape of the data before the Union but if my guess is right, you're feeding in 7 streams of data. If you use a sequence of Joins instead of the Union (Join Stream 1 and Stream 2 followed by the output of the first join joined with Stream 3 etc), this will result in all of the values being on the same row. You will then be able to use the Formula tool to generate the value you need.

 

join.png

Highlighted
Pulsar

Hi @JLEE038 

 

Based on the data you provided, an easy way to do this is to isolate the week values (the columns that begin with D1) from the values you want to multiply them by (the columns containing the word "data"). Did this with a Dynamic Select. A Dynamic Rename then uses a RegEx Replace formula to rename the columns with just the numbers contained in them. This facilitates the join. Then a formula tool to multiply, and a summarize tool to add.

 

image.png


Let me know if this helps.

 

Cheers!

Esther

Meteoroid

Thanks @EstgerB47 !  Sorry for the late reply, was OOO. 

 

This works !  I am currently using @MichalM solution while I try to figure out RegEX and the dynamic tools. Will try to improvise on my workflow once I master the 2 functions.  

Meteoroid

Thanks !  This works and I am currently using your solution for my workflow. ! Sry for the late reply as I was OOO. 

Labels