Multiple variable by a CONSTANT variable (no related)

[luisccmm]

I have one variable call action_types that are around 40 activities with a related conversion rates, but those activities are not related to the web visits they are like a constant value over the time, and need to multiple this constant value by the web visits.

How to multiple 2 variables when they are not in fact related in the script, although one is expected to operate as a constant?

I have the following two tables in the script.

Conversion_rates:

LOAD

Action_types

Conversion_rates

FROM: .... .xlsx

Visits:

LOAD

Date

Visits,

FROM .... xlsx

There are around 40 Action Type and each of it has its own conversion rates (From table Converson Rates) I need to create a formula similar to the following:

Conversion_rates * sum( Visits) per day   

So the function above multiples the value of the conversion_rate of the action_type by the production on a specific date.

Conversion_rates:

Action_type      Conversion_Rates

Action1                   0,2%

Action2                   0,3%

Action 3                 0,4%

Visits:

Date                   Visits

01/02/19            2000

02/02/19            3000

....

04/02/19             5000

04/02/19              7000

....

EXPECTED RESULT in a Date Table

Date                    Function_RESULT

01/02/19            2000*(0,2% +0,3%+0,4%...)

02/02/19            3000*(0,2% +,3%+0,4%....)

04/02/19            5000*(0,2% +,3%+0,4%....)

Regards

[vunguyenq89 · Accepted solution]

Hi,

You can calculate the sum of all conversion rates in table Conversion_rates, store it into a variable and use it in later calculation. For example:

Conversion_Rates:
LOAD Action_type, Conversion_Rates
FROM [lib://Data/Data.xlsx] (ooxml, embedded labels, table is Conversion_Rates);

Conversion_Rates_Sum:
LOAD Sum(Conversion_Rates) as Conversion_Rates_Sum Resident Conversion_Rates;

Let vConversion_Rates_Sum = Peek('Conversion_Rates_Sum');

Visits:
LOAD Date, Visits 
FROM [lib://Data/Data.xlsx] (ooxml, embedded labels, table is Visits);

Conversions:
LOAD Date, 
     Sum(Visits) * $(vConversion_Rates_Sum) as SumConversion
Resident Visits
Group By Date;

Hope this helps!

BR,

Vu Nguyen