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).
[Item Amount] as Amount,
FROM TestBlackline.xlsx (ooxml, embedded labels);
DATE(MAX(PeriodDate)) AS MaxPeriodDate
(i have attached a copy of the dataset and QVW file). thanks!
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?
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?
@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:
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
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 ];
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.