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

Help Needed with Table Structure

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

10 Replies
satheshreddy
Creator III
Creator III

Hi Maino,

Can you share the sample column names, we can easily get a solution.

Regards

Sathish

annafuksa1
Creator III
Creator III

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

davidla_
Contributor II
Contributor II

Hola Matt Maino

podrias subir un excell con la algunos datos para ver la forma de apoyarte.

saludos.

Not applicable
Author

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
davidla_
Contributor II
Contributor II

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

satheshreddy
Creator III
Creator III

3 tables Account name are same?

if same unique use a normal join

Not applicable
Author

Account Numbers are the same

vishsaggi
Champion III
Champion III

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;

Not applicable
Author

This duplicates accounts by a factor of cards times transactions