topic Re: Help Needed with Table Structure in QlikView

Help Needed with Table Structure

Wed, 11 Jan 2017 15:30:35 GMT

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

Re: Help Needed with Table Structure

satheshreddy — Wed, 11 Jan 2017 15:34:18 GMT

Hi Maino,

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

Regards

Sathish

Re: Help Needed with Table Structure

annafuksa1 — Wed, 11 Jan 2017 15:35:49 GMT

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

Re: Help Needed with Table Structure

davidla_ — Wed, 11 Jan 2017 15:40:09 GMT

Hola Matt Maino

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

saludos.

Re: Help Needed with Table Structure

Wed, 11 Jan 2017 15:44:12 GMT

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

Re: Help Needed with Table Structure

davidla_ — Wed, 11 Jan 2017 15:58:31 GMT

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

Re: Help Needed with Table Structure

satheshreddy — Wed, 11 Jan 2017 16:05:11 GMT

3 tables Account name are same?

if same unique use a normal join

Re: Help Needed with Table Structure

Wed, 11 Jan 2017 16:07:18 GMT

Account Numbers are the same

Re: Help Needed with Table Structure

vishsaggi — Wed, 11 Jan 2017 16:13:39 GMT

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;

Re: Help Needed with Table Structure

Wed, 11 Jan 2017 16:48:02 GMT

This duplicates accounts by a factor of cards times transactions

Re: Help Needed with Table Structure

davidla_ — Wed, 11 Jan 2017 17:57:09 GMT

Matt Maino.

podrias subir un archivo de Excell donde venga informacion de cada una de las tablas y mostar un ejemplo para poder ayudarte y llegar al resultado que quieres.

saludos.

David Lino