Linked Table problem with one to many

[johnpaul]

Hi all,

I have only recently been working with linked tables and thought that I had the hang of it.

I have followed many of the examples shown, but can't seem to work out what I am doing wrong.

Basically when I link tables with one to many relationships I don't get the data in every field.

Please see the attached example.

Thanks,

Jp.

[#2]

look your data in excel is not correct thats why your linking is incorrect because:

> the key through which one table is link to another table must be a primary key and foreign key in another table means no null values is allowed in primary key and values must be distinct.

> but your values in primary key is repeating.

hope you understand.

[hic]

If you really want to use a link table, then you cannot use NULL values in it. Instead I think you need to define a symbol for "any" - e.g. the string '<ANY>' - and map that to all values in the dimension.

However, in this specific case I would not use a link table. I would instead just use the tables the way they are. It is true that CustomerID will create an unacceptable loop. But this is a problem that could be solved in a different way:

If Ticket.Customer and Invoice.Customer are always the same, then you do not need to load the Customer in the invoice table. And if they could be different, then you should load the Customers from both tables, but under different names

[johnpaul]

Thanks for your help.

The ticket is always assigned to a customer, but may be billed to a different customer/supplier

The situation which I want to report on is similar to a service company doing warranty work for a car manufacturer.

For example, I have purchased my BMW and it is under warranty.

I take my car in for a routine service.

They open one ticket for the serivce which will be billed to me.

I also tell them about the window that doesn't automatically wind up when I lock the door as it should. They open another ticket which is for my car, so I am the customer for the ticket, but the invoice will go to BMW.

In the above example, this would probably require a new field called InvoicePayer - who pays for the ticket.

So, I agree that my example may have mislead people, but it was only to try to show a circular loop problem.

What I get as a result is:

The Red rectangle shows that the CustomerID is blank where I want it to be 'Z' as in the Blue rectangle below.

Also, I want to remove the row as shown by the green rectangle.

My data was a bit messed up, as I had a ticket which belonged to one customer and billed to another as in the above car example. This would in a real situation require different fields.

So, now that I have fixed the data, the result is still a problem:

Here is the data:

And the result I get is still the same, there are too many rows:

The linkage looks OK:

This is my linked table load script:

LOAD TicketNumber as TicketKey,
     Description
//     CustomerID // cannot use this as there will be a loop - so use the linked table.
FROM
linktabletest.xlsx
(ooxml, embedded labels, table is Tickets);

LOAD BillingID as InvoiceKey,
     invoiceNumber
//     CustomerID
FROM
linktabletest.xlsx
(ooxml, embedded labels, table is Invoices);

LOAD
  // TicketNumber as TicketKey,
           TimeID as TimeKey,
  // TicketNumber,
     Hours,
     BillingID
FROM
linktabletest.xlsx
(ooxml, embedded labels, table is Time);

// Linked Table

LinkTable:
LOAD DISTINCT TicketNumber as TicketKey,
          Null() as TimeKey,
          Null() as InvoiceKey,
          TicketNumber,
    CustomerID // cannot use this as there will be a loop - so use the linked table.
FROM
linktabletest.xlsx
(ooxml, embedded labels, table is Tickets);

LinkTable:
LOAD DISTINCT
           TicketNumber as TicketKey,
           TimeID as TimeKey,
           BillingID as InvoiceKey,
     TicketNumber,
           null() as CustomerID
FROM
linktabletest.xlsx
(ooxml, embedded labels, table is Time);

Attached is a new version of the .XLS.

thanks,

Jp.