Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi you can create a list box with the expression:
=Aggr(DISTINCT Only({<date={'>$(=AddMonths(Today(), -48))'}>} ID_customer), ID_customer)
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
Hi,
is this can be a start for you?
Regards
André Gomes
Ok, then maybe something like:
=Aggr(If(Count({<date={'>$(=AddMonths(Today(), -48))'}>} DISTINCT Year(date))=4, ID_customer), ID_customer)
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.
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...
Hi Diego,
i hope my answer as in somehow helped you.
Regards
André Gomes
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.
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!!