topic Re: Data Island calculation in QlikView

Data Island calculation

Anonymous — Mon, 30 Nov 2015 20:30:18 GMT

I'm looking for the appropriate formula to include in a chart expression.

Here is an example set of data. I have 3 data islands.

Data Island 1

Parts Price

PartA 10

PartB 20

PartC 30

Data Island 2

Parts Currency

PartA USD

PartB USD

PartC EURO

Data Island 3

Currency Rate

EURO 1.09

Island 2 identifies the currency in which Island 1's prices are entered. I would like to create a table chart that lists prices by part all in USD. Therefore, if the parts currency is already USD then just show the price but if the Currency is Euro then it needs to take the price and multiply by the rate. The chart table should result in:

Parts Price

PartA 10

PartB 20

PartC 32.70

Re: Data Island calculation

satishkurra — Mon, 30 Nov 2015 20:53:46 GMT

Check the attached file, You can customize to yours...

Thanks

Satish

Re: Data Island calculation

ElizaF — Mon, 30 Nov 2015 21:06:56 GMT

Hello Mark,

One solution is to use the applymap function in Script.

See the attach file.

Hope this helps.

Re: Data Island calculation

maxgro — Mon, 30 Nov 2015 22:53:13 GMT

I would just add a line to your script, the one in bold

SET ThousandSep=',';

SET DecimalSep='.';

DataIsland1: load * inline [

Parts Price

PartA 10

PartB 20

PartC 30

] (delimiter is spaces);

DataIsland2: load * inline [

Parts Currency

PartA USD

PartB USD

PartC EURO

] (delimiter is spaces);

DataIsland3: load * inline [

Currency Rate

EURO 1.09

] (delimiter is spaces);

Concatenate (DataIsland3) load 'USD' as Currency, 1 as Rate AutoGenerate 1;