QlikView Scripting

[hopkinsc]

also, the calc needed to work out the vMaxEndDate for previous years can be ignored, i have just realised that i have variables for these that i can re use.

so its just getting help defining the start dates for each year.

so you have helped getting the flags set against each year

0 = current

-1 = last year

-2 = previosu yeat

etc

what i need is to get an if statement written to pick up all dates >= the date that has the flag of 0 <= vMaxEndDate and set this flag as 0.

e.g.

If(Date>=date that has flag of 0 and Date<='$(vMaxEndDate)',0,

     If(Date>=date that has flag of -1 and Date<='$(vMaxEndDateLY)',-1,

etc

im unsure how to get the 'date that has flag of 0' part

[sunny_talwar]

Make sense, look at it now...

[sunny_talwar]

May be this

LET vMaxEndDate = '09/10/2017';

Dates:

LOAD CALENDAR_DDMMYYYY,

    CALENDAR_YEAR,

    CALENDAR_MONTH,

    CALENDAR_DAY,

    CALENDAR_WEEK_NO,

    WEEKDAY,

    FISCAL_PERIOD,

    FISCAL_YEAR,

    FISCAL_PERIOD_NO,

    FISCAL_WEEK_NO,

    AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,

    CALENDAR_MONTH_TEXT

FROM DATES.qvd (qvd);

Left Join (Dates)

LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,

FISCAL_YEAR

Resident Dates

Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;

Max:

LOAD Max(WeekNum) as MaxWeekNum

Resident Dates

Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';

LET vMaxWeekNum = Peek('MaxWeekNum');

LET vLoop = Floor($(vMaxWeekNum)/53);

DROP Table Max;

FOR i = 0 to $(vLoop);

TRACE $(vLoop);

LET vCalc = $(vMaxWeekNum) - ($(i)*53);

TRACE $(vCalc);

Table:

LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,

FISCAL_YEAR

Resident Dates

Where WeekNum = $(vCalc)

Group By FISCAL_YEAR;

NEXT i;

Left Join(Dates)

LOAD *

Resident Table;

DROP Table Table;

FinalDates:

LOAD *,

If(Month(CALENDAR_DDMMYYYY) >= 7 and Day(CALENDAR_DDMMYYYY) >= Day(YearStartDateMonday) and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag

Resident Dates;

DROP Table Dates;

[hopkinsc]

Hi Sunny,

thanks for this.

i see a problem though.

when selecting fiscal year 2018, i should have 0 against all dates from 03/07/2017-09/10/2017

but i am not getting a 0 on any of the 01 or 02 of each month. so its only flagging from 03 onwards.

its the same for the rest of the years too.

is this easily fixed?

[sunny_talwar · Accepted solution]

My bad, try this

LET vMaxEndDate = '09/10/2017';

Dates:

LOAD CALENDAR_DDMMYYYY,

    CALENDAR_YEAR,

    CALENDAR_MONTH,

    CALENDAR_DAY,

    CALENDAR_WEEK_NO,

    WEEKDAY,

    FISCAL_PERIOD,

    FISCAL_YEAR,

    FISCAL_PERIOD_NO,

    FISCAL_WEEK_NO,

    AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,

    CALENDAR_MONTH_TEXT

FROM DATES.qvd (qvd);

Left Join (Dates)

LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,

FISCAL_YEAR

Resident Dates

Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;

Max:

LOAD Max(WeekNum) as MaxWeekNum

Resident Dates

Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';

LET vMaxWeekNum = Peek('MaxWeekNum');

LET vLoop = Floor($(vMaxWeekNum)/53);

DROP Table Max;

FOR i = 0 to $(vLoop);

TRACE $(vLoop);

LET vCalc = $(vMaxWeekNum) - ($(i)*53);

TRACE $(vCalc);

Table:

LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,

FISCAL_YEAR

Resident Dates

Where WeekNum = $(vCalc)

Group By FISCAL_YEAR;

NEXT i;

Left Join(Dates)

LOAD *

Resident Table;

DROP Table Table;

FinalDates:

LOAD *,

If(Month(CALENDAR_DDMMYYYY) >= 7 and CALENDAR_DDMMYYYY >= YearStartDateMonday and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag

Resident Dates;

DROP Table Dates;

[hopkinsc]

Thanks so much Sunny. that works!

i wont bother you again (this week )

have a nice xmas all!