2 The table has two columns with the same meaning

[Anonymous]

Hi All, I am newbie in Qlikview.
I have a problem about cyclic table, table Structure The following details:

is there any suggestion to solve this problem?

Billing

Billing Number **

PeriodMonthBilling

PeriodYearBilling

CurrencyCode *

AmountBilling

Collection

Collection Number

PeriodMonthCollection

PeriodYearCollection

CurrencyCode *

AmountCollection

BankFrom

BIllingNumber  **

Currency

CurrencyCode *

PeriodMonth

PeriodYear

Exchange rate

[CELAMBARASAN]

Hi,

     Remove Currency code field from any one table other than Currency table.

     Or

     You can go with this

Billing

Billing Number **

PeriodMonthBilling

PeriodYearBilling

CurrencyCode *

AmountBilling

Billing Number & CurrencyCode as KeyField

Append Billing Number & CurrencyCode and make that as key field.

Collection

Collection Number

PeriodMonthCollection

PeriodYearCollection

BIllingNumber  & CurrencyCode as KeyField

AmountCollection

BankFrom

Append Billing Number & CurrencyCode as Key field Remove the Billing number and Currency code fields.

Currency

CurrencyCode *

PeriodMonth

PeriodYear

Exchange rate

Celambarasan

[Anonymous]

Dear Celambarasan,

Thanks for your  fast and accurate answer.

Sorry, but my table does not have enough information.

Your answers are very helpful, but not answered my question because of errors Foreign Key (my information is wrong)

The correct table :

Note : PeriodMonthBilling/PeriodYearBilling not same as PeriodMonthCollection/PeriodYearCollection

Billing

Billing Number **

PeriodMonthBilling  *

PeriodYearBilling  *

CurrencyCode *

AmountBilling

Collection

Collection Number

PeriodMonthCollection  *

PeriodYearCollection  *

CurrencyCode *

AmountCollection

BankFrom

BIllingNumber  **

Currency

CurrencyCode *

PeriodMonth *

PeriodYear *

Exchange rate

Regards,

Sinbad001

[CELAMBARASAN]

Hi,

Can you tell me brief about the joining between 3 tables?

What info you get from the currency table?

If possible join the info from the Currency table to the billing and collection table but make sure that you changed the field names differently according to the tables.

Celambarasan

[Anonymous]

relationship of these three tables are

table billing is a data about bill, after bill is paid it will be noted in the table collection.

table currency is used to record the value of the currency exchange rate at the time of billing or at the time carried on doing paid.

Is there a solution other than using a join?

Do Join the Qlikview pretty fast when compared to regular table link?

[CELAMBARASAN]

Hi,

     I think currency code will be same for all billing number in both billing and collection table is it?

     If so you can remove the currency code from billing table or in collection table.

Hope it solves your problem

Celambarasan

[Anonymous]

Hi Celambarasan,

Thanks for your quick answer. 

CurrencyCode is the same, but if the transaction is carried out in different months, then the exchange rate can be different. So for the billing and collection can have a different exchange rate. (Example: billing can occur in the month of january 2012, but the collection took place in February 2012).

Regards,

Sinbad001

[CELAMBARASAN · Accepted solution]

Hi,

     If so then the only way is you have to join the Exchange rate to both the tables bill and collection.

Celambarasan