Creating one table out of two tables

[#1]

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))

;

[spsrk_84]

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

[#3]

Hi Ajay143,

It works, thanks a lot.

Jan