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: 
Not applicable

How to calculate "Last Purchase Year" in transaction table load script (based on only prior data)

Howdy,

So here's my scenario,

Standard transnational table.

Load

Order_Date,

Order_Year,

Customer#,

Revenue

From Excel;

I want to calculate a new column for each record in this table that finds the last purchase year for each customer (only looking at previous years of data, less than & not equal to the current year)

i.e. if we're looking at a customer's transaction in 2012, and their last purchase year (not including 2012) is 2010, that's the data i want to bring into the new column.

Sample file attached,

Much thanks!

-David

1 Solution

Accepted Solutions
maxgro
MVP
MVP

tmp:

load distinct Customer#, Order_Year Resident Data;

tmp1:

load 

Customer#, Order_Year, if(Customer#=peek(Customer#), peek(Order_Year)) as Max_Previous_Order_Year

Resident tmp

order by Customer#, Order_Year asc;

drop table tmp;

1.png

View solution in original post

10 Replies
Clever_Anjos
Employee
Employee

Please check if fits your needs

Data:

LOAD Order_Date,

     Order_Year,

     Customer#,

     Revenue

FROM

(ooxml, embedded labels, table is Data);

join load 

  Order_Year as Aux,

     Customer#

Resident Data;

LOAD

  Order_Date,

  Order_Year,

  Customer#,

    Revenue,

    Aux as LastPurchaseYear

Resident Data   

Where Aux < Order_Year;

alexandros17
Partner - Champion III
Partner - Champion III

Try with this example

AAA:
LOAD * Inline [
Anno, Cust
2013, c1
2014, c1
2015,c1
2014, c2
2015, c3
]
;

BBB:
NoConcatenate
LOAD Anno, Cust, If(Anno < 2015, 1, 0) as Flag Resident AAA;
DROP Table AAA;

Left Join
LOAD Cust, Max(Anno) as Maxi Resident BBB Where Flag=1 Group By Cust;

Not applicable
Author

Thank you for your reply! Can you please help me understand what you did?

When i run this script, i get almost 20k records, starting with 1838

Not applicable
Author

I want to load all the original records,

1) if this is the customers first year of purchase, make it null()

2) If they have a previous year of purchasing, I want to know what that last year was prior to this one

alexandros17
Partner - Champion III
Partner - Champion III

Try my example

Not applicable
Author

I was going to, but it has hard-coded years in it and I don't know how to overcome that.

Not applicable
Author

This is unfortunately returning their max order year, and not the "max order year prior to the year of the transaction"

Not applicable
Author

Howdy! I have attached a sample QVW, Pretty confused on this inter-record stuff.

For each transaction, i need to calculate the last year (Prior to the year of that transaction) that there was a transaction.

-David

maxgro
MVP
MVP

tmp:

load distinct Customer#, Order_Year Resident Data;

tmp1:

load 

Customer#, Order_Year, if(Customer#=peek(Customer#), peek(Order_Year)) as Max_Previous_Order_Year

Resident tmp

order by Customer#, Order_Year asc;

drop table tmp;

1.png