Scripting issue

[rassanbekov]

Hello,

please help!

I have two files, in which I have both "Codes".

The table 1 includes data with "Codes", "Name" and "amount"

The table 2 includes data with "Codes" and "percentage".

Requried formula: amout x percentage.

The issue is that both table do not match 100%. In case when the table 2 has not data on Codes, the formula should calculate percentage as 40%.

Please assist, which script should be written.

I tried the formula If(Percentage = 0, sum(amount*40%), sum(amount*percentage)). But it didn't work.

Kind regards,

Ruslan

[effinty2112]

Hi Ruslan,

try something like

MappingPercentage:

Mapping

Load

Code,

Percentage

From [table 2]

Table1:

Load

Code,

Name,

Amount,

Amount*ApplyMap('MappingPercentage',Code,40)/100 as PCAmount

From Table1;

The table MappingPercentage is a mapping table that will disappear after the load script has executed, no need to drop it. The applymap function will look up the Percentage value corresponding to the code in the mapping table. If there is no Percentage value corresponding to the code then the default value of 40 will be applied.

Good luck

Andrew

[rassanbekov]

Thanks but what if I have more than two columns to upload? what is the substitute function?

[qvuser2012]

I'd say you could continue with loading your table 2. The mapping will just help you calculate the a new field and run a calculation.  If you need to map another set of data to use in a second calculation then you simply create another mapping with similar syntax.

[vamsee]

You might've to create two mapping loads.

(or)

Join the tables with a distinct primary key.

Table1:

Load

Code,

Name,

Amount

From Table1;

Join (Table1)

Load Code,

Column1,

Column2

From Table2;

[effinty2112 · Accepted solution]

No substitute function, create a mapping table for each column and use them all in a single join operation.