Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

AddMonths Function in Load Script

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!

 

 

 

 

 

 

 

 

 

 

 

 

6 Replies
Highlighted

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?

Highlighted
Creator
Creator

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!

Highlighted

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

Highlighted
Creator
Creator

@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:

AccountAmountPeriodDatePeriodKey
A$$$12/31/20181
B$$$2/28/20191
C$$$5/30/20182

 

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

Highlighted

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 
];
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.