topic Re: Data Relationships in QlikView

Data Relationships

Tue, 10 May 2011 10:02:09 GMT

I have a pivot table showing a list of customers. The expressions give me values for Sales - GP - GP%.

Currently I need to add another column which encorporates an exchange rate. My problem is that these rates are being sucked in from a spreadsheet. (There is a different exchange rate for each month.)

When i bring in the exchange rate it only appears for the total values and there is a 0 value for each customer.

Does anyone know what i can do to have each customer's exchange rate showing?

Thanks

Re: Data Relationships

Tue, 10 May 2011 10:20:47 GMT

Re: Data Relationships

Tue, 10 May 2011 15:29:17 GMT

Apologies if my first post was confusing. Basically I want to have the exchange rate, as seen in the picture, be populated all the way down the column... I'll post a clearer picture here.

Thanks in advance to anyone who can shed any light on what my problem is...

Anyone know why is the exchange rate only being picked up by certain lines?

Data Relationships

Tue, 10 May 2011 20:10:44 GMT

Post a picture of the table viewer b/c I'm not sure how it's associated if at all without seeing a picture.

Re: Data Relationships

Wed, 11 May 2011 08:34:21 GMT

Thanks for the reply!

The ex_val is my exchange rate...

This is the result in my table....

Data Relationships

Wed, 11 May 2011 09:00:05 GMT

I suggest you to load your exchange rate table not in a new table, but in the "DateParts" table using a

LEFT JOIN (DateParts)

LOAD DISTINCT Date, ex_val FROM your Excel file;

After that, you are sure to have 1 rate (maximum) for each date.

It's hard to understand what you are trying to do because every picture are cropped to hide your column headers...

Does anyone know what i can do to have each customer's exchange rate showing?

The part I don't understand is that an exchange rate is based on a date, but in your chart, you don't have a dimension "date".

You can display a customer exchange rate only if you have only 1 date linked to your customer !

Data Relationships

prieper — Wed, 11 May 2011 09:00:13 GMT

Does the table with the exchange-rate have an entry for each date?

Otherwise you need to modify the date in the "DateParts"-table to be inline with the external table, check the MONTHSTART-function or the like and link the exchange-rates to this field then.

Otherwise please post an example of your application.

HTH
Peter

Re: Data Relationships

Wed, 11 May 2011 09:15:44 GMT

Apologies for the confusion, there is a dimension for date in my document, its basically a listbox displaying months. I also have a list box with the exchange rates.

In my spreadsheet where the values for the exchange rates are stored, there is a different exchange rate for each month. Thus, when I click on a month in my month listbox, the exchange rate is shown on the exchange rate listbox.

The exchange rates are NOT linked to customers, they're linked to months.

I hope this clears it up some of the confusion. Thanks

Re: Data Relationships

Wed, 11 May 2011 09:38:15 GMT

Brian,

Then create a Month link in the calendar.

Something like this:

Calendar Table	Exchange Rate Table
Month(Date) As Month
Month(Date) As LinkMonth	Month(Date) As LinkMonth
	ExchangeRate

If you have no calendar, then left join load the Exchange rate table into the fact table with LinkMonth as the common data element.

Regards,

Rich

Re: Data Relationships

Wed, 11 May 2011 09:53:34 GMT

Thanks for the reply Richard. I already have the exchange rate linked to the appropriate month. My issue is that the exchange rate is not being applied to each customer in my pivot table. For example in the below picture, I have March 2011 highlighted on my listbox, the correct exchange rate is being shown, however it is only being applied to a certain amount of customers.

I want to be in a position where I can manipulate the Value with the exchange rate but currently im not in a position to do so. Words cannot express my frustration!

Re: Data Relationships

Wed, 11 May 2011 10:06:08 GMT

My issue is that the exchange rate is not being applied to each customer in my pivot table.

As you said, there is no 1 exchange rate linked to your customer.

QlikView will sometimes find more than 1 value available for the same customer.

"More than 1 value" is displayed as "-" in a QlikView chart.

Even if it's the same value, this values occurs as many times as you have values.

Try to use

=only(ex_val)

in your chart.

This fonction will display the value if it's a single value that occurs more than 1 time.

If you still not have what you expect, use

=concat(ex_val, ', ')

in your chart.

This will display you all the values available for each customer. This will help you to understand what is wrong with your model.

Note that "concat(ex_val, ', ')" will display every possible values, whereas "concat(distinct ex_val, ', ')" will display every distinct possible values.

Re: Data Relationships

Wed, 11 May 2011 10:08:45 GMT

Brian,

Can you post a table object with customer, month, exchange rate and value?

Rich

Re: Data Relationships

Wed, 11 May 2011 13:50:28 GMT

Sorry for the delay in getting back to you. Here is a screenshot of the table structure.

I'm at a loss...

Data Relationships

Wed, 11 May 2011 14:15:02 GMT

Brian,

Is JJ_Date in DataParts a rename of the Invoice Date or the month of the invoice date?

If it is an invoice date does Sheet1$ contain one and only one representative date of every invoice date in your Invoice File or just a month end/begin date?

Does Sheet1$ contain multiple rates for the same date or are there missing dates within Sheet1$ that are in the invoice table?

Try this. Create a Table Object with Customer, Invoice Date, JJ_Date and ex_val to debug the association of the data between the tables.

This should help you see if there are missing and/or multiple JJ_Dates in your Sheet1$ table.

I suspect the problem lies in the data.

Rich

Re: Data Relationships

Wed, 11 May 2011 14:30:09 GMT

Thanks Richard,

Is JJ_Date in DataParts a rename of the Invoice Date or the month of the invoice date?

In the code for DateParts I have... Date As invoice_date

Date As JJ_Date

If it is an invoice date does Sheet1$ contain one and only one representative date of every invoice date in your Invoice File or just a month end/begin date?

In Sheet1$ has a seperate date format again, It basically starts at Jan-07 and goes all the way to May-11. Each value has a unique rate.

Does Sheet1$ contain multiple rates for the same date or are there missing dates within Sheet1$ that are in the invoice table?

Only June & July 2011 have the same exchange rate. In my chart this is posing problems but have used the =only(ex_val) function to help this.

Yes, its definitely an issue with the data and the relationships, its been kind of trial and error so far trying to find a solution.

Re: Data Relationships

Thu, 12 May 2011 09:52:32 GMT

Just to update the thread, I found a solution to this problem for anyone who is interested.

Basically I stored the exchange rate value in a variable (vExRate = ex_val) and it worked.

I'm now having some small issues with the fact that some months have the same exchange rates but i dont think it will take me as long to get to the bottom of that problem.

Thanks again for your help, much appreciated.