Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
erjohnso
Contributor

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.

Tags (1)
1 Solution

Accepted Solutions
s_kunte23
Contributor III

Re: Consecutive left joins?

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
s_kunte23
Contributor III

Re: Consecutive left joins?

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

erjohnso
Contributor

Re: Consecutive left joins?

Thank you! Concatenate! Of course!

Partner
Partner

Re: Consecutive left joins?

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.