topic Re: Get the currency value i need in QlikView

Get the currency value i need

Thu, 10 Sep 2015 09:12:57 GMT

Hello everyone,

i have a problem i cant resolve on my own, hoping for help.

Situation is i have 3 tables, that are connected by:

Sales Line - has all information regarding the sales

connected via "Billing Number"

Sales Header - has the "currency code" value (for the Billing number)

connected via "currency code"

Currency on Day - has all 4 currencys (EUR,CAD,USD,CHF) and their value per Day (f. ex. 01.09.2015 has one value for that day for every currency)

I tried to connect the Currency on Day table via Day(Date) and currency Code. But i cant, because there would be loops.

The table i need looks like this:

Billing Number - Billing Date - Currency Code - Amount (expression= sum(neto))

I have it and it works fine, but now we started to sell stuff in foreign currency and my Database(Navision) gives me only the foreign currency amount and thats wrong for my reports.

My Question: What do i need to write into the Expression for Amount if i want to get the right numbers?

I want the expression to check the currency Code (also can be blank) and calculate = sum(neto) * Currency on Day(Billing Date) IF the currency code is NOT Blank.

I appreciate any help.

Thank you.

Kristian

Re: Get the currency value i need

lironbaram — Thu, 10 Sep 2015 09:20:29 GMT

my first step would have been to merge the header and lines table to one table

this shouldn't create any data integrity issues

then you would be able to create the date&currency code key

and work with him

Re: Get the currency value i need

Thu, 10 Sep 2015 09:29:13 GMT

Hi Liron,

ty for your answer. The problem is i never did this in the Start (should have..). To change this now, would be a huge amount of work rewriting almost every module and stats i finished.

This can be done earliest in summer. I need now a possibility somehow to get the numbers i need via expressions. If its possible of course.

Re: Get the currency value i need

lironbaram — Thu, 10 Sep 2015 11:01:04 GMT

so let me see if i understand the situation

currently you have the for any line\amount

the date is part of the line

we know the currency code through the connection to header

so the connection between the line and currency table display the currency rate for one coin for all the dates

so your expression should be something like

sum(amount)* if(CurrencyDate=BillDate,CurrencyRate)

Re: Get the currency value i need

Thu, 10 Sep 2015 11:16:17 GMT

The currency on day table - has Billdate, Exchange rate value and Currency

So basically yeah, the table has to show me every line and if currency code something else then blank, it has to get the exchange rate for this day in this currency and calculate.

Re: Get the currency value i need

lironbaram — Thu, 10 Sep 2015 11:29:01 GMT

can you upload a sample data

all i need the relevant fields from the 3 tables with one row of dummy data

Re: Get the currency value i need

jonathandienst — Thu, 10 Sep 2015 11:49:30 GMT

Assuming the following:

SalesLine:

LOAD [Billing Number], // Assume [Billing Number]/[LineNo] is unique...

[LineNo],

neto,

...

SalesHeader:

LOAD [Billing Number], // Assume [Billing Number] is unique...

[Currency Code],

Day,

...

CurrencyOnDay:

LOAD Day, // Assume [Day] is unique...

EUR,

CAD,

USD,

CHF

...

Then add the code:

T_Convert:

NoConcatenate

LOAD [Billing Number],

LineNo,

neto

Resident SalesLine;

Join (T_Convert)

LOAD [Billing Number],

[Currency Code],

Day

Resident SalesHeader;

Join (T_Convert)

LOAD Day,

EUR,

CAD,

USD,

CHF

Resident CurrencyOnDay;

Join (SalesLine)

LOAD [Billing Number],

LineNo,

neto * Pick(Match([Currency Code], 'EUR', 'CAD', 'USD', 'CHF'), EUR, CAD, USD, CHF) As netoUSD,

Pick(Match([Currency Code], 'EUR', 'CAD', 'USD', 'CHF'), EUR, CAD, USD, CHF) As ExchRate

Resident T_Convert;

DROP Table T_Convert;

netoUSD contains the neto amount converted to USD. Note that the conversion may be division depending on how the rates are recorded, or you may want to load the rates all divided by USD (or the inverse), again depending on what the rates look like. The USD - USD rate should always be 1.

Re: Get the currency value i need

Thu, 10 Sep 2015 11:56:34 GMT

Here you go. If there is no "Currency" label, then its just sum(amount).

Re: Get the currency value i need

lironbaram — Thu, 10 Sep 2015 12:18:39 GMT

attach is a demo that works

but as i use aggr functions , it might cause performance issues

if you have a lot of data , if you can't join header and lines may be

you can add the currency field to the lines by left join and then work with a direct key to the currency table