Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am creating a document with 3 tables and am struggling with joining them together properly.
The tables are:
Accounts - with account level detail
Cards - with debit card level detail like card number (one to many relationship with accounts but not all accounts have cards)
Transactions - transaction level detail (one to one relationship with cards but not all transactions are made with cards and many to one relationship with accounts but not all accounts have transactions)
The Cards table needs to join to the accounts table via Account Number and Date without duplicating accounts
The Transactions table needs to join to the Accounts table via Account Number and Date without duplicating accounts
The Transactions table and Cards table need to join together via Date and Card Number but not all transactions are made with a card
Does anyone know what this join would look like?
If I left join cards to accounts it duplicates the accounts with as many cards that are on the account.
Thanks
Hi Maino,
Can you share the sample column names, we can easily get a solution.
Regards
Sathish
I will recommend to leave 3 tables as if you will join these table as there is relationship one to many it will multiple your table (its correct )
second option is limited 'many table' for example in table Cards pick only newts card (one card for one account) and this table join to Account table.
Anna
Hola Matt Maino
podrias subir un excell con la algunos datos para ver la forma de apoyarte.
saludos.
Accounts |
---|
Account Number |
Production Date |
Name |
Current Balance |
Address |
Plan Number |
Cards |
---|
Card Number |
Account Number |
Production Date |
Exp Date |
ATM Limit |
Card Status |
Transactions |
---|
Account Number |
Production Date |
Card Number (if card transaction) |
Transaction Amount |
Transaction Time |
Transaction Type |
Matt.
De acuerdo a los ejemplos que estas cargando, lo que puede ayudarte para no crear llaves sinteticas entre estas tres tablas es crear una sola tabla por medio de un outer join de esta manera podra generar todas las posibles relaciones entre ellas.
Accounts:
load *
Nombre Tabla;
Cards:
outer join(Accounts)
load *
Nombre Tabla;
Transacciones:
outer join(Accounts)
load *
Nombre Tabla;
intentalo y me platicas.
saludos.
David Lino
3 tables Account name are same?
if same unique use a normal join
Account Numbers are the same
May be Try this?
Accounts:
LOAD AutoNumberHash128([Account Number]&[Production Date], 1) AS %Key_AccountProdDate,
[Account Number],
[Production Date],
Name,
[Current Balance],
Address,
[Plan Number]
FROM Accounts;
Cards:
LOAD AutoNumberHash128([Account Number]&[Production Date], 1) AS %Key_AccountProdDate,
[Card Number],
[Account Number] AS CardAccountNumber,
[Production Date] AS CardProductionDate,
[Exp Date],
[ATM Limit],
[Card Status]
FROM Cards;
Transactions:
LOAD AutoNumberHash128([Account Number]&[Production Date], 1) AS %Key_AccountProdDate,
[Account Number] AS TranAccountNumber,
[Production Date] AS TranProductionDate,
[Card Number] AS TransactionCardNumber,
[Transaction Amount],
[Transaction Time],
[Transaction Type]
FROM Transactions;
This duplicates accounts by a factor of cards times transactions