2 Replies Latest reply: Feb 1, 2011 5:50 PM by jancoenen RSS

    Creating one table out of two tables

    jancoenen

       

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

      ;

       

       

        • Creating one table out of two tables
          Shivarama krishna K

          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