Multiplication Union data

[JLEE038]

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 ! 

Month

Weel before first 10 days

Week before 20 days

Week before 30 days

Week before EOM

D1 No. of Days Week 1

D1 No. of Days Week 2

D1 No Of Days Week 3

1st Week Data

2nd Week Data

3rd Week Data

4th Week Data

5th Week Data

6th WeekData

5/1/2019

1

2

4

5

4

6

0

[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 solution]

@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 solution]

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

[JLEE038]

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]

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