topic Re: Consecutive left joins? in QlikView

Consecutive left joins?

erjohnso — Mon, 03 Nov 2014 19:59:53 GMT

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.

Re: Consecutive left joins?

Anonymous — Mon, 03 Nov 2014 20:10:31 GMT

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

Re: Consecutive left joins?

erjohnso — Mon, 03 Nov 2014 20:11:48 GMT

Thank you! Concatenate! Of course!

Re: Consecutive left joins?

jpapador — Mon, 03 Nov 2014 20:12:49 GMT

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.