Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiplication Union data

JLEE038
7 - Meteor

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
4 REPLIES 4
MichalM
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

estherb47
15 - Aurora
15 - Aurora

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

JLEE038
7 - Meteor

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.  

JLEE038
7 - Meteor

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

Labels