10 Replies Latest reply: Jan 13, 2015 2:11 PM by David Cohen

# 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.

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

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

Data:

Order_Year,

Customer#,

Revenue

FROM

(ooxml, embedded labels, table is Data);

Order_Year as Aux,

Customer#

Resident Data;

Order_Date,

Order_Year,

Customer#,

Revenue,

Aux as LastPurchaseYear

Resident Data

Where Aux < Order_Year;

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

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

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

Try with this example

AAA:
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;

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

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

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

Try my example

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

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

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

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

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

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

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

tmp:

load distinct Customer#, Order_Year Resident Data;

tmp1:

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;

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

Amazing! Thank you for your help!