Multiply a Column with Value in Data set 1 with All the Columns of another Data Set 2

[Achint]

I have two separated data sets DS1 and DS2 as below. DS1 has one column with data value in it which I need to multiply with all the data columns in DS2 and create new columns accordingly to DS2 (Multiplied columns)

DS1:

DS2:

Since the number of rows are same in both DSs, we need to directly multply Row 1 of DS1 with Row 1 of DS2, etc

Example: Factor * Q1 2021, Factor * Q2 2021, etc

Appreciate your help here as I have experience of 2 days in Alteryx.

Let me know if the requirement is not clear

[ShankerV]

Hi @Achint 

Step 1: Join both the datasets using Join tool. Use join by Record Position.

Step 2: Use the formula tool to calculate the values.

Factor * Q1 2021

Factor * Q2 2021

Many thanks

Shanker V

[DataNath · Accepted solution]

Hey @Achint, with the absence of some sample data, I've mocked up something quickly here. Input data:

If the row counts and positions truly line up as you mention, you can do a very simple join based on the record position:

Now, as your data appears to contain characters that will make fields a string data type (comma, $ sign etc), we'll need to strip these out and convert the field to a numeric data type so we can treat is as such and multiply it by the factor. As you have several fields, we can also handle them all at once using the Multi-Field Formula tool. Here's one way I'd go about this:

This expression is first of all replacing anything that isn't a number (character 0-9), with nothing. From your example I have assumed that there'll only ever be whole numbers here. However, if not, just add a decimal into the squared brackets of the Regex_Replace() function i.e. [^0-9.]. The next function - working outwards - temporarily converts the result of this to a number, which is then multiplied by the [Factor] field, before being rounded to the nearest integer.

You'll notice that in the middle of the configuration, I've created new fields that are suffixed with '*Factor'. You can change this as you wish, or just untick that box entirely so your original fields are replaced rather than making new ones.

Hope this helps - please feel free to shout if you have questions or need anything else!

[martinding]

Hi @Achint,

Here is one way to do it:

[Achint]

Thanks @Shanker for the solution. The solution looks to be manual as i;ll have to create multiple calculated field for all the Qn 202n column which is going to be dynamic. Although really appreciate your help on it. God speed!

[cjaneczko]

If the data is going to be dynamic then you might want to look at using the Multi-Field Formula after the join. You can choose to keep the original values or uncheck the box to overwrite to what the formula output is. Any new columns that come in will get multiplied by the factor as the quarters change. 

EDIT: I did not see the post above from DataNath, but he explains it in detail there.

[Achint]

Thanks DataNath for the solution. This works like a charm, just that i had to update the regex as per my need. So used this below instead:
Round(ToNumber(IF IsEmpty([_CurrentField_]) THEN 0 ELSE [_CurrentField_] ENDIF)*[Factor],1)

Appreciate your help on this. Have a great day ahead! Cheers!

[Achint]

Thanks cjaneczko for the solution, yes you and DataNath have the same dynamic solution. Cheers to both of you.

[Achint]

Hi martinding

Thanks for the solution, you solution works too buyt in my case somehow when i try to re-transpose after transposing, it screws up my data format, hence the solution wont be appropriate in my case. But appreciate your time on this. Cheers!