Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Get the currency value i need

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

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hi

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

View solution in original post

8 Replies
lironbaram
Partner - Master III
Partner - Master III

hi

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

Not applicable
Author

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.

lironbaram
Partner - Master III
Partner - Master III

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)

Not applicable
Author

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.

lironbaram
Partner - Master III
Partner - Master III

can you upload a sample data

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

hi

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