Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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;

View solution in original post

sunny_talwar

Did you ever get to check this?