Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Current, New and Lost Customers

Good morning,

I need a little help finding current, new and lost customers.  The first part of the script finding old customer and new customers seems to work, but the lost customers I am having a hard time with.  Any ideas?

Thanks

LEFT JOIN (VBRK_R)
LOAD [Billing Date],
FISYR
RESIDENT FiscalCalendar;

LEFT JOIN (VBRP_R)
LOAD %BillingDocument_Key,
[Billing Date],
FISYR
RESIDENT VBRK_R;

Old_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
[Sold To] AS [Old Sold To],
1
AS Old_Cust
RESIDENT VBRP_R
WHERE FISYR >= '$(vPrevYear)' and
FISYR < '$(vFisYr)'
;

New_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
1
AS New_Cust
RESIDENT VBRP_R
WHERE FISYR = '$(vFisYr)'
AND NOT EXISTS([Old Sold To], [Sold To])
;

Lost_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
1
AS Lost_Cust
RESIDENT VBRP_R
WHERE FISYR <= '$(vLastServiceYear)'
AND NOT EXISTS ([Old Sold To], [Sold To])
;

1 Solution

Accepted Solutions
sunny_talwar

Try this

Table:

LOAD * INLINE [

    Customer, Year

    A, 2016

    B, 2016

    C, 2016

    B, 2017

    C, 2017

    D, 2017

];

Old_Customer:

LOAD DISTINCT Customer,

Customer as Old_Customer,

1 as Old_Cust

Resident Table

Where Year <= 2016;

New_Customer:

LOAD DISTINCT Customer,

Customer as New_Customer,

1 as New_Cust

Resident Table

Where not Exists(Old_Customer, Customer) and Year = 2017;

Temp:

LOAD DISTINCT Customer as Temp_Customer,

1 as New_Cust

Resident Table

Where Exists(Old_Customer, Customer) and Year = 2017;

Lost_Customer:

LOAD Distinct Customer,

1 as Lost_Cust

Resident Table

Where not Exists(Temp_Customer, Customer) and Year <= 2016;

DROP Table Temp;

View solution in original post

10 Replies
sunny_talwar

May be this

Lost_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
1
AS Lost_Cust
RESIDENT VBRP_R
WHERE FISYR <= '$(vLastServiceYear)';

What if you do the lost without the Where statement

tmumaw
Specialist II
Specialist II
Author

Thanks.  I will give it a try.

sunny_talwar

My bad, that isn't going to work... thinking about it now...

tmumaw
Specialist II
Specialist II
Author

But it could be an old customer

sunny_talwar

Would you be able to share few rows of data to test this out? I am difficulty imagining this with the additional complexity of the variables you have which I don't really know expand to?

tmumaw
Specialist II
Specialist II
Author

Way too much data to have to share to get the results.

sunny_talwar

I was just looking for 10 rows may be

sunny_talwar

Try this

Table:

LOAD * INLINE [

    Customer, Year

    A, 2016

    B, 2016

    C, 2016

    B, 2017

    C, 2017

    D, 2017

];

Old_Customer:

LOAD DISTINCT Customer,

Customer as Old_Customer,

1 as Old_Cust

Resident Table

Where Year <= 2016;

New_Customer:

LOAD DISTINCT Customer,

Customer as New_Customer,

1 as New_Cust

Resident Table

Where not Exists(Old_Customer, Customer) and Year = 2017;

Temp:

LOAD DISTINCT Customer as Temp_Customer,

1 as New_Cust

Resident Table

Where Exists(Old_Customer, Customer) and Year = 2017;

Lost_Customer:

LOAD Distinct Customer,

1 as Lost_Cust

Resident Table

Where not Exists(Temp_Customer, Customer) and Year <= 2016;

DROP Table Temp;

sunny_talwar

Did you ever get to check this?