1 Reply Latest reply: Jun 15, 2016 9:16 AM by Oliver Krause RSS

    Completion of incomplete table incl. exception handling

    Oliver Krause

      Dear all,

       

      I have a table-completion issue I cannot solve…

       

      STEP 1

      I load a project milestone table with 3 milestones per project "FS, SF and SAB".

      (Normally each milestone is at Thuresday noon time, so that the company can do SW updates on Thu evening and still be sure automated tests run over the weekend.)

       

      In cases a Thuresday is public holiday (or other issues) a milestone may be shifted to any other day in the week (normally Wed, but could be also Tue or Fri…)

       

       

      STEP 2

      I then need to complete the table with intermediate-milestones - one per week, the intermediate are always on Thuredays

       

       

      STEP 3

      I need to add the calendar-week to each milestone (but not "normal" calendar week - it counts always the week Thu noon to Thu noon is one week. And in case the normally-Thuresday-milestone is on a Wednesday, I need it to be the same calendar week as if it was Thu…

       

      STEP 4

      When loading data (later on) I need to match the data to this modified calendar week…

       

       

      The attached script works perfectly if all milestones are on Thuresdays.

      But how to adapt it to make it work for Tue/Wed/Fri-cases, too??

       

      See attached example QVW

       

      The output I would need after load is like this:

       

      %I_Stufe.NAME

      %IWeek

      1. IStufenWk.MS
      2. IStufenWk.Date

      Stufe1-Mi

      21

      FS

      Do 26.05.2016

      Stufe1-Mi

      22

      FS+1

      Do 02.06.2016

      Stufe1-Mi

      23

      SF

      Do 09.06.2016

      Stufe1-Mi

      24

      SF+1

      Do 16.06.2016

      Stufe1-Mi

      25

      SAb

      Do 23.06.2016

       

       

       

       

      Stufe2-Do

      20

      FS

      Mi 25.05.2016

      Stufe2-Do

      22

      FS+1

      Do 02.06.2016

      Stufe2-Do

      23

      SF

      Do 09.06.2016

      Stufe2-Do

      24

      SF+1

      Do 16.06.2016

      Stufe2-Do

      25

      SAb

      Do 23.06.2016

       

       

       

       

      Stufe3-Di

      22

      FS

      Di 31.05.2016

      Stufe3-Di

      23

      SF

      Do 09.06.2016

      Stufe3-Di

      24

      SF+1

      Do 16.06.2016

      Stufe3-Di

      25

      SF+2

      Do 23.06.2016

      Stufe3-Di

      26

      SF+3

      Do 30.06.2016

      Stufe3-Di

      27

      SAb

      Do 07.07.2016

       

       

       

       

      Stufe4-short

      22

      SF/FS

      Do 02.06.2016

      Stufe4-short

      23

      SF/FS+1

      Do 09.06.2016

      Stufe4-short

      24

      SF/FS+2

      Do 16.06.2016

      Stufe4-short

      25

      SF/FS+3

      Do 23.06.2016

      Stufe4-short

      26

      SAb

      Fr 01.07.2016

       

       

      Thanks a lot

      Oliver

        • Re: Completion of incomplete table incl. exception handling
          Oliver Krause

          Sorry, in case you are working with QV personal edition and cannot open the qvw, attached you find the complete script of the QVW I attached above:

           

           

          SET ThousandSep='.';
          SET DecimalSep=',';
          SET MoneyThousandSep='.';
          SET MoneyDecimalSep=',';
          SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
          SET TimeFormat='hh:mm:ss';
          SET DateFormat='DD.MM.YYYY';
          SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
          SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
          SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';


          SET eIsWk  = If ( (Week(WeekStart($1,0,4)) + if(WeekDay($1)='Do' AND Hour($1)>=12,1,0) )=0,53, (Week(WeekStart($1,0,4)) + if(WeekDay($1)='Do' AND Hour($1)>=12,1,0) ) );  // Takes 1 parameter & Returns IStufenWoche Do1200-Do1159. TRIAL: Woche startet Freitag und für Do nachmittag die +1. Daher muss MC_Date immer +1 genommen werden vor Verwendung der Funktion, sonst wird Fr12-Fr1159 gerechnet...


          SWL_I_Stufe:
          LOAD * INLINE [
          %I_Stufe.NAME,SWL_I_Stufe.DATUM_FS,SWL_I_Stufe.DATUM_SF,SWL_I_Stufe.DATUM_SAB
          'Stufe1-Mi','26.05.2016','09.06.2016','23.06.2016'
          'Stufe2-Do','25.05.2016','09.06.2016','23.06.2016'
          'Stufe3-Di','31.05.2016','09.06.2016','07.07.2016'
          'Stufe4-short','02.06.2016','02.06.2016','01.07.2016'
          ]
          ;


          // --- Lade I-Stufen-Termine als Crosstable
          [ISt_Termine]:
          CrossTable (MS,Date)
          LOAD
          [%I_Stufe.NAME],
          If ( Date("SWL_I_Stufe.DATUM_FS")=Date("SWL_I_Stufe.DATUM_SF"),Date("SWL_I_Stufe.DATUM_SF"),'') as [SF/FS], // sollte nicht FS/SF sein, da sonst nicht erkannt wird, dass die Sollkurve hier schon steigt
             If ( Date("SWL_I_Stufe.DATUM_FS")<Date("SWL_I_Stufe.DATUM_SF"),Date("SWL_I_Stufe.DATUM_FS"),'') as FS,
          If ( Date("SWL_I_Stufe.DATUM_FS")<Date("SWL_I_Stufe.DATUM_SF"),Date("SWL_I_Stufe.DATUM_SF"),'') as SF,
          Date("SWL_I_Stufe.DATUM_SAB"as SAb
          Resident SWL_I_Stufe;

          // --- Fülle fehlende Wochen auf (FS+1,etc.)
          LOAD  [%I_Stufe.NAME],
          Date(Date+IterNo()*7)  as Date,
          If( Not(MS='SAb'),MS & '+' &Iterno(),'')  as MS
          While Date+IterNo()*7 < PreviousDate

          LOAD  *,
          Previous(Date) as PreviousDate
          Resident ISt_Termine
          Order By [%I_Stufe.NAME],Date desc;


          // Erzeuge IStufen-KW zu den Terminen
          LEFT JOIN (SWL_I_Stufe)
          LOAD  [%I_Stufe.NAME],
          MS   as IStufenWk.MS,
          Date(Date+0.5)  as IStufenWk.Date,
          $(eIsWk(Date+0.5))  as %IWeek,
          if( (MS='FS' OR MS='SF/FS'), 'xx',  // Sonderfall: FS=Kein SOLL für die Woche bis FS
            ( if (MS='SF', mid([%I_Stufe.NAME],12,1)&'F',  // Sonderfall: SF=Soll für die Woche vorher=FS-Soll.
             mid([%I_Stufe.NAME],12,1)&left(MS,1))) )  as [%ConfigParam]   // Prevent match with "FS", da dort die Sollwerte 0 sein sollen
          Resident ISt_Termine;

          DROP Table ISt_Termine;