Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rassanbekov
Partner - Contributor
Partner - Contributor

Scripting issue

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

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

5 Replies
effinty2112
Master
Master

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
Partner - Contributor
Partner - Contributor
Author

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

qvuser2012
Contributor II
Contributor II

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
Specialist
Specialist

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
Master
Master

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