Consecutive left joins?

[erjohnso]

Having trouble with something that seems simple but isn't working...

Here is a simplified example of the data where we have multiple customers and customers can have multiple stores:

Store ID

Customer ID

Revenue

To track and trend "same store" growth, I want to flag new and lost customers and stores. I started with a simple left join to add in new stores for existing customers:

Store ID

Status

But if we get 2 new customers, it is easier to flag an entire customer ID with a left join rather than drop 100s of Store IDs into the file:

Customer ID

Status

So my code looks something like this...

Roster:

Load

Store ID,

Customer ID,

Revenue

FROM

[..\QVD\Roster.qvd]

(qvd);

Left Join(Roster)

LOAD

    Store ID,

     Status

FROM

[..\External Data\Adds.xlsx]

(ooxml, embedded labels, table is Store);

Left Join(Roster)

LOAD

    Customer ID,

     Status

FROM

[..\External Data\Adds.xlsx]

(ooxml, embedded labels, table is Customer);

The resulting roster table does not capture the second left join. Is there something wrong with the logic of this approach? Or do I need to scrub my code? Thanks in advance.

[Anonymous · Accepted solution]

Hi,

you can't join this table in Roster because there are already the two fields CustomerID and Status.  depending on what your data is about you could concatenate the last table:

Roster:

Load

Store ID,

Customer ID,

Revenue

FROM

[..\QVD\Roster.qvd]

(qvd);

Left Join(Roster)

LOAD

    Store ID,

     Status

FROM

[..\External Data\Adds.xlsx]

(ooxml, embedded labels, table is Store);

concantenate (Roster)

LOAD

    Customer ID,

     Status

FROM

[..\External Data\Adds.xlsx]

(ooxml, embedded labels, table is Customer);

or join the last table renaming the field status:

concantenate (Roster)

LOAD

    Customer ID,

     Status as StatusCustID

FROM

[..\External Data\Adds.xlsx]

(ooxml, embedded labels, table is Customer);

you could then try (but this depends on your data)

New_Status:

load*,

if(isnull(Status), StatusCustID, Status) as Status_new

Resident Roster;

drop table Roster;

drop fields Status, StatusCustID from New_Status;

Hope this helps!

Best regards

Stefan

[erjohnso]

Thank you! Concatenate! Of course!

[jpapador]

It is because it is trying to join where both customer ID and status match (synthetic key).  Since it is a left join, if it finds no matches (where both status and customer id match) it wont bring in any records.