Creating a cross table / creating missing rows in load script

[#1]

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!

[#2]

Any one able to help with this?

[Josh_Good]

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;

    Next;