Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like a load script to determine the earliest opening of an account from a list of a number of customers with multiple accounts. Is there a way to compare the customers purchases to determine whether a certain product was purchased prior to another? Ultimately, I'd like to be able to determine which customers purchased product A that have not yet purchased Product B, but also which customers have purchased both with A being the first product purchased. This is what I am working with...
NoConcatenate
FINAL:
Load
CIN, //customer id number
[Account Number] ,
[Account Type], //products
date([Purchase Date],'MM/DD/YYYY') as [Open Date]
Resident Accounts;
left join
Load
CIN,
min([Open Date]) as [Earliest Bank Account Open Date]
Resident Accounts
Group By [CIN];
Is that script getting any trouble?
No, the script is working. Just need a method to accomplish tasks described.
Can you describe little in clear, Please?
I'm looking for something along the lines of...
if([Earliest Open Date]=[Open Date] and [Account Type]='A','Yes','No') as [Earliest Product A]
but this doesn't seem to work.
From there, I want to know: Of Customers who have purchased both types A and B which had earliest Account Type = A and which had earliest Account Type = B.
May be create The As-Of Table
Or
Sample:
Load [Earliest Open Date],
[Open Date],
[Account Type]
From Table;
Sample_Cp:
NoConcatenate
LOAD [Account Type] as [Account Type Cp], [Earliest Open Date], [Open Date]
Resident Sample
Where [Account Type] = 'A';
Concatenate(Sample_Cp)
LOAD [Account Type] as [Account Type Cp], [Earliest Open Date], [Open Date]
Resident Sample
Where [Account Type] = 'B' AND Exists ([Account Type Cp], [Account Type]);
DROP Table Sample_Cp;
RENAME Field [Account Type Cp] to [Account Type];