Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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
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
Try my example
I was going to, but it has hard-coded years in it and I don't know how to overcome that.
This is unfortunately returning their max order year, and not the "max order year prior to the year of the transaction"
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
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;