AddMonths Function in Load Script

[triekong]

I have a dataset file, but I want to add a calculated column to the dataset, based on the max date field present in the entire dataset.

I have brought in the data, and have gotten the max(date field). Now, I want to add my calculated periodkey field to the dataset and don't know how to do this.

on the front end, I am able to derive this value using AddMonths() function, but I want this to be included in the script, so each Account will have a corresponding periodkey in the final table. (not trying to sum anything up).

Current Script:

BlacklineFileTemp:
LOAD Account,
[Item Amount] as Amount,
PeriodDate
FROM TestBlackline.xlsx (ooxml, embedded labels);

Temp:
LOAD

DATE(MAX(PeriodDate)) AS MaxPeriodDate

RESIDENT BlacklineFileTemp;

(i have attached a copy of the dataset and QVW file). thanks!

[sunny_talwar]

So, all you want it to find the 6 Month Ago, 12 Month Ago, 18 Months Ago, and 24 Months Ago from your PeriodDate field for all Account? Can you have more than one Account with different PeriodDates? In this case you would want to go 6, 12, 18, 24 Month back from the max date?

[triekong]

hi @sunny_talwar yes. i want to find the 6 Month Ago, 12 Month Ago, 18 Months Ago, and 24 Months Ago but from the most recent PeriodDate field in the entire dataset, which is given by the Max(PeriodDate).

1 account can never have more than 1 PeriodDate.

does this explain it better?

thanks!

[sunny_talwar]

But you mentioned that you wanted these new dates for each account?

[triekong]

@sunny_talwar  no problem. let me explain better.

so for every account, there is 1 PeriodDate. and for each periodDate, I want 1 PeriodKey.

Based on the most recent PeriodDate in the entire dataset, I want to include a new column, called "PeriodKey". this will depend on if the Account's PeriodDate was between 6months ago, 12months ago, 18months ago or 24months ago, from the Most Recent Date of the entire dataset. 

so for example, if my MaxPeriodDate is "02/28/2019", and i have an account with the date of "12/31/2018", (this is 3months prior to the most recent PeriodDate), so it happened within the last 6months.

this Account should now have a PeriodKey of 1. so the new column to be created in the end result dataset, should look like this:

Account	Amount	PeriodDate	PeriodKey
A	$$$	12/31/2018	1
B	$$$	2/28/2019	1
C	$$$	5/30/2018	2

The logic for the periodkey is in the script on the attached file

if 0-6months ago -->periodKey 1, if 7-12months ago -->periodKey 2, if 13-18months ago -->periodKey 3, if 19-24months ago --> periodKey 4

what i need help with, is how to include this calculation in the load script and create the column

[sunny_talwar]

May be try this

BlacklineFileTemp:
LOAD Account, 
	[Item Amount] as Amount, 
	PeriodDate
FROM TestBlackline.xlsx
(ooxml, embedded labels);

Left Join (BlacklineFileTemp)
LOAD Max(PeriodDate) as MaxPeriodDate
Resident BlacklineFileTemp;

BlacklineFile:
LOAD *,
	 If(PeriodDate >= AddMonths(MaxPeriodDate, -6) and PeriodDate <= AddMonths(MaxPeriodDate, 0), 1,
	 If(PeriodDate >= AddMonths(MaxPeriodDate, -12) and PeriodDate < AddMonths(MaxPeriodDate, -6), 2,
	 If(PeriodDate >= AddMonths(MaxPeriodDate, -18) and PeriodDate < AddMonths(MaxPeriodDate, -12), 3,
	 If(PeriodDate >= AddMonths(MaxPeriodDate, -24) and PeriodDate < AddMonths(MaxPeriodDate, -18), 4)))) as periodkey
Resident BlacklineFileTemp;

DROP Table BlacklineFileTemp;

PeriodMapping:
LOAD * INLINE [
    periodkey, Period
    1, Mon 0-6 
    2, Mon 7-12 
    3, Mon 13-18 
    4, Mon 19-24 
];

[Brett_Bleess]

Ekong, did Sunny's latest post with sample app get you what you needed?  If so, please be sure to give credit by clicking the Accept as Solution button on that post...  

If you still need further help, leave an update.

Regards,
Brett