Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
erjohnso
Creator
Creator

Consecutive left joins?

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

3 Replies
Anonymous
Not applicable

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
Creator
Creator
Author

Thank you! Concatenate! Of course!

jpapador
Partner - Specialist
Partner - Specialist

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.