topic Re: AddMonths Function in Load Script in QlikView

AddMonths Function in Load Script

triekong — Sat, 16 Nov 2024 20:25:23 GMT

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!

Re: AddMonths Function in Load Script

sunny_talwar — Thu, 18 Jul 2019 18:35:54 GMT

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?

Re: AddMonths Function in Load Script

triekong — Thu, 18 Jul 2019 18:52:51 GMT

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!

Re: AddMonths Function in Load Script

sunny_talwar — Thu, 18 Jul 2019 18:54:36 GMT

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

Re: AddMonths Function in Load Script

triekong — Thu, 18 Jul 2019 19:44:02 GMT

@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

Re: AddMonths Function in Load Script

sunny_talwar — Fri, 19 Jul 2019 13:20:35 GMT

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 ];

Re: AddMonths Function in Load Script

Brett_Bleess — Wed, 14 Aug 2019 14:13:16 GMT

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