Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Client fidelity check

Hi guys,

I'm trying to create a script for qlikview but I have some problems.

I'd like to check if a customer bought items last four years (at least one per year). What I have right now:

-db table1 with ID_customer

-db table2 with ID_customer, purchase, date of purchase

Es:

table1

ID_customer

1

3

5

7

10

12

14

15

table2

ID_customer purchase date

1 car 12/01/2011

1 tires 12/01/2012

1 motor 18/04/2013

1 glass 19/09/2014

1 tires 12/01/2012

5 parfum 05/07/2009

10 water 30/11/2014

What I'd like create is a list with customers that bought last four years, in this example ID_customer 1

Can anyone help me?

Cheers,

Diego

Tags (1)
1 Solution

Accepted Solutions
consenit
Contributor II

Re: Client fidelity check

Hi there.

Try this:

Customers:

LOAD * INLINE

[

  ID_customer

  1

  3

  5

  7

  10

];

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, value, date

  1, car, 10000, 12/01/2011

  1, tires, 400, 12/01/2012

  1, motor, 5000, 18/04/2013

  1, glass, 300, 19/09/2014

  1, tires, 400, 12/01/2012

  5, parfum, 80, 05/07/2009

  10, water, 10, 30/11/2014

];

MaxYear:

LOAD

  MAX(YEAR(date)) AS MaxYear

RESIDENT Purchase;

LET vMaxYear =FLOOR(PEEK('MaxYear',0));

DROP TABLE MaxYear;

PurchaseByYear:

LOAD

  ID_customer,

  YEAR(date) as year,

  IF(YEAR(date) >= $(vMaxYear) -4, 1, 0) AS FlagLastYears,

  COUNT(purchase) as TotalPurchase,

  SUM(value) as TotalAmount

RESIDENT Purchase

GROUP BY

  ID_customer,

  YEAR(date),

  IF(YEAR(date) >  $(vMaxYear) -4, 1, 0);

ClientFidelity:

LOAD

  ID_customer,

  SUM(FlagLastYears) AS Fidelity

RESIDENT PurchaseByYear

WHERE TotalPurchase > 0

GROUP BY ID_customer;

LEFT JOIN (Customers) LOAD

  ID_customer,

  IF(Fidelity >= 4, 1, 0) AS Fidelity

RESIDENT ClientFidelity;

DROP TABLE ClientFidelity;

It's basically the same script with an added SUM() of all the values by year and without the last DROP TABLE statement.

Kind regards,

Ernesto.

18 Replies

Re: Client fidelity check

Hi you can create a list box with the expression:

=Aggr(DISTINCT Only({<date={'>$(=AddMonths(Today(), -48))'}>} ID_customer), ID_customer)

Not applicable

Re: Client fidelity check

Hi,

thanks for the answer, but it's not what I'm trying to do. What I need is to know people that bought at least one item for each year (of the past X years). In your example, you find ID 1 (correct) and ID 10 that bought only last year

agomes1971
Valued Contributor

Re: Client fidelity check

Hi,

is this can be a start for you?

Regards

André Gomes

Re: Client fidelity check

Ok, then maybe something like:

=Aggr(If(Count({<date={'>$(=AddMonths(Today(), -48))'}>} DISTINCT Year(date))=4, ID_customer), ID_customer)

consenit
Contributor II

Re: Client fidelity check

Hi there.

I think you can do all the calculations on the script. Try this:

Customers:

LOAD * INLINE

[

  ID_customer

  1

  3

  5

  7

  10

  12

  14

  15

];

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, date

  1, car, 12/01/2011

  1, tires, 12/01/2012

  1, motor, 18/04/2013

  1, glass, 19/09/2014

  1, tires,12/01/2012

  5, parfum, 05/07/2009

  10, water, 30/11/2014

  1, glass, 07/01/2015

];

PurchaseByYear:

LOAD

  ID_customer,

  YEAR(date) as year,

  IF(YEAR(date) >= YEAR(TODAY())-4, 1, 0) AS FlagLastYears,

  COUNT(purchase) as TotalPurchase

RESIDENT Purchase

GROUP BY

  ID_customer,

  YEAR(date),

  IF(YEAR(date) > YEAR(TODAY()-4), 1, 0);

ClientFidelity:

LOAD

  ID_customer,

  SUM(FlagLastYears) AS Fidelity

RESIDENT PurchaseByYear

WHERE TotalPurchase > 0

GROUP BY ID_customer;

LEFT JOIN (Customers) LOAD

  ID_customer,

  IF(Fidelity >= 4, 1, 0) AS Fidelity

RESIDENT ClientFidelity;

DROP TABLE ClientFidelity;

DROP TABLE PurchaseByYear;

This will generate a flag field "Fidelity" in the customers table signaling if such customer purchased something in the past 4 years including current year.

Kind Regards,

Ernesto.

Not applicable

Re: Client fidelity check

Hi Ernesto,

thanks for the reply but it seems not to be working.. If you try to change

1, glass, 07/01/2015

to

1, glass, 07/01/2014,


no customers should have the flag Fidelity settet to one (as no one bought on 2015).. But it still happens...

agomes1971
Valued Contributor

Re: Client fidelity check

Hi Diego,

i hope my answer as in somehow helped you.

Regards

André Gomes

consenit
Contributor II

Re: Client fidelity check

Yes, I had to add an entry for at least one purchase in 2015 to make it work because my original calculation was based on the TODAY() function wich returns the current date. Don't worry: it's easy to fix though:

Customers:

LOAD * INLINE

[

  ID_customer

  1

  3

  5

  7

  10

  12

  14

  15

];

Purchase:

LOAD * INLINE

[

  ID_customer, purchase, date

  1, car, 12/01/2011

  1, tires, 12/01/2012

  1, motor, 18/04/2013

  1, glass, 19/09/2014

  1, tires,12/01/2012

  5, parfum, 05/07/2009

  10, water, 30/11/2014

];

MaxYear:

LOAD

  MAX(YEAR(date)) AS MaxYear

RESIDENT Purchase;

LET vMaxYear =FLOOR(PEEK('MaxYear',0));

DROP TABLE MaxYear;

PurchaseByYear:

LOAD

  ID_customer,

  YEAR(date) as year,

  IF(YEAR(date) >= $(vMaxYear) -4, 1, 0) AS FlagLastYears,

  COUNT(purchase) as TotalPurchase

RESIDENT Purchase

GROUP BY

  ID_customer,

  YEAR(date),

  IF(YEAR(date) > YEAR(TODAY()-5), 1, 0);

ClientFidelity:

LOAD

  ID_customer,

  SUM(FlagLastYears) AS Fidelity

RESIDENT PurchaseByYear

WHERE TotalPurchase > 0

GROUP BY ID_customer; 

LEFT JOIN (Customers) LOAD

  ID_customer,

  IF(Fidelity >= 4, 1, 0) AS Fidelity

RESIDENT ClientFidelity;

DROP TABLE ClientFidelity;

DROP TABLE PurchaseByYear;

Kind regards,

Ernesto.

Not applicable

Re: Client fidelity check

Hi Ernesto,

what I was saying is that if you remove the 2015 date in your previous script, it still gives you a flag value setted to 1 for the customer (I think it's an error). Anyway I can study and work using the code you posted, so thank you so much!!

Community Browser