Multiplication Union data

Question

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

MichalM · Accepted Answer

@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.

estherb47 · Accepted Answer

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.

Let me know if this helps.

Cheers!

Esther