Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Partner
Partner

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
Highlighted
Honored Contributor

Re: Scripting issue

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
Highlighted
Honored Contributor

Re: Scripting issue

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

Highlighted
Partner
Partner

Re: Scripting issue

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

Highlighted
New Contributor II

Re: Scripting issue

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.

Highlighted
Valued Contributor

Re: Scripting issue

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;

Highlighted
Honored Contributor

Re: Scripting issue

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

View solution in original post