topic Re: Creating a cross table / creating missing rows in load script in QlikView

Creating a cross table / creating missing rows in load script

Thu, 26 Sep 2013 14:22:32 GMT

Hi experts,

I have a set of data relating to currency conversion rates to one currency - my sample data is as follows (where all currencies map to USD)

LOAD * INLINE

[

Source, Target,Rate

'AUD','USD',1.0491804516129

'CNY','USD',0.160729677419355

'EUR','USD',1.32941558064516

'GBP','USD',1.59668225806452

'IDR','USD',0.000103774193548387

'INR','USD',0.0184186129032258

'JPY','USD',0.0112268387096774

'NZD','USD',0.836179064516129

'PLN','USD',0.32105735483871

'RUB','USD',0.0330604516129032

'TWD','USD',0.0344044193548387

'USD','USD',1

'ZAR','USD',0.113732612903226

];

What I am looking for is a way to map the currenices directly to each other without having to go through USD - in this sample data I would want to see that EUR to AUD gives a conversion rate of 1.267 (the EUR to USD rate / AUD to USD rate). This would then be repeated for all other currency combinations so that all source / target pairs exist within the data.

I would like to be able to create all permutations within the LOAD script rather than within charts if this is at all possible.

Thanks!

Re: Creating a cross table / creating missing rows in load script

Mon, 30 Sep 2013 10:52:46 GMT

Any one able to help with this?

Re: Creating a cross table / creating missing rows in load script

Josh_Good — Wed, 02 Oct 2013 21:14:58 GMT

Try this script. I've also attached the QVW for your reference.

ExchangeRatesSource:

LOAD * INLINE

[

Source, Target, Rate

'AUD', 'USD', 1.0491804516129

'CNY', 'USD', 0.160729677419355

'EUR', 'USD', 1.32941558064516

'GBP', 'USD', 1.59668225806452

'IDR', 'USD', 0.000103774193548387

'INR', 'USD', 0.0184186129032258

'JPY', 'USD', 0.0112268387096774

'NZD', 'USD', 0.836179064516129

'PLN', 'USD', 0.32105735483871

'RUB', 'USD', 0.0330604516129032

'TWD', 'USD', 0.0344044193548387

'USD', 'USD', 1

'ZAR', 'USD', 0.113732612903226

];

Let varNumberOfCurrencies = NoOfRows('ExchangeRatesSource');

Let j = 0;

For i = 0 to $(varNumberOfCurrencies) -1

Let varSourceCurrency = Chr(39) & Peek('Source', $(i), 'ExchangeRatesSource') & Chr(39);

for j = 0 to $(varNumberOfCurrencies) -1

Let varTargetCurrency = Chr(39) & Peek('Source', $(j), 'ExchangeRatesSource') & Chr(39);

Load $(varSourceCurrency) as Source,

$(varTargetCurrency) as Target,

Peek('Rate', $(i), 'ExchangeRatesSource') / Peek('Rate', $(j), 'ExchangeRatesSource') as Rate

AutoGenerate 1;

Next;