Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've two tabeles: Persbudget and Time
I need a table PersbudgetMonth looking like this
0001 201101 5000
0001 201102 5000
0001 201103 5000
0002 201102 6000
0002 201103 6000
0002 201104 6000
0002 201105 6000
How do I fix this challange?
The scripting for those tables is:
PersBudget:
LOAD * Inline [Persnr, StartPer, EindPer, Budget
0001, 201101, 201103, 5000
0002, 201102, 201105, 6000];
LET StartDate = num(date(yearstart(addmonths(today(),-36,0)),'YYYYMMDD'));
LET EndDate = num(date(yearend(addmonths(today(),0,0)),'YYYYMMDD'));
Time:
LOAD
Date AS %Key_Date,
Date AS Date,
num(year(Date)) AS [Year],
dual('Q'&num(ceil(num(month(Date))/3)),num(ceil(num(month(Date))/3),00)) AS [Quarter],
month(Date) AS [Month],
num(month(Date),00) AS [Month Num],
num(week(Date),00) AS [Week],
year(Date)&dual('Q'&num(ceil(num(month(Date))/3)),num(ceil(num(month(Date))/3),00)) AS YearQuarter,
year(Date)&num(month(Date),00) AS YearMonth,
year(Date)&num(week(Date),00) AS YearWeek,
day(Date) AS [Day],
day(Date) & '-' & month(Date) AS [Day Month],
weekday(Date) AS [WeekDay]
;
LOAD
date($(StartDate)+(iterno()-1),'YYYYMMDD') AS Date
AUTOGENERATE 1
WHILE date($(StartDate)+(iterno()-1)) <= date($(EndDate))
;
Hi,
i got the solution for you query .....:)
Please go through the code avilable below
ABX:
LOAD * Inline [Persnr, StartPer, EindPer, Budget
0001, 201101, 201103, 5000
0002, 201102, 201105, 6000
0003, 201105, 201107, 7000];
AAA:
LOAD MAX(Persnr) AS MXP,
MIN(Persnr) AS MINP
RESIDENT ABX;
LET A=PEEK('MINP',0,'AAA');
LET AA=PEEK('MXP',0,'AAA');
for a='$(A)' to '$(AA)'
SDF:
Load Persnr as P, StartPer AS StPr, EindPer As EndPR RESIDENT ABX where Persnr='$(a)';
LET I=PEEK('StPr',0,'SDF');
LET J=PEEK('EndPR',0,'SDF');
for b='$(I)' to '$(J)'
Final:
load
Persnr,
'$(b)' AS StartPer,
Budget
RESIDENT ABX where Persnr='$(a)';
next
DROP TABLE SDF;
next
DROP TABLE ABX;
Paste the above code in the application and test it will generate the records in your required format...
Regards,
Ajay
Hi Ajay143,
It works, thanks a lot.
Jan