Completion of incomplete table incl. exception handling

[#1]

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	IStufenWk.MS 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

[#2]

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;