topic Re: Scripting issue in QlikView

Scripting issue

rassanbekov — Thu, 24 Aug 2017 15:28:52 GMT

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

Re: Scripting issue

effinty2112 — Thu, 24 Aug 2017 16:33:29 GMT

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

Re: Scripting issue

rassanbekov — Thu, 24 Aug 2017 17:58:41 GMT

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

Re: Scripting issue

qvuser2012 — Thu, 24 Aug 2017 18:15:30 GMT

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.

Re: Scripting issue

vamsee — Thu, 24 Aug 2017 18:21:18 GMT

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;

Re: Scripting issue

effinty2112 — Thu, 24 Aug 2017 18:36:11 GMT

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