Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Completion of incomplete table incl. exception handling

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

1 Reply
Not applicable

Re: Completion of incomplete table incl. exception handling

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;