Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a cross table / creating missing rows in load script

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 Replies
Not applicable
Author

Any one able to help with this?

Josh_Good
Employee
Employee

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;