Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

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

Any one able to help with this?

Highlighted
Employee
Employee

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

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;