topic Re: resident load & where statement in QlikView

resident load & where statement

carolin01 — Wed, 11 Dec 2013 10:19:15 GMT

Hello,

I´ve the following issue:

I´ve loaded a table from an excel spreadsheet showing each dates and assigning our coporate fiscal year:

CorporateCalendar:

LOAD

date(floor(Date)) as OverallTransactionDate,
     FWeekday,
     FWeeksPerPeriod,
     FWeek,
     FPeriod,
date#(FPeriod & FYear,'MM.YYYY') as Datetmp,
     FPeriod & FYear as FYP,
     Fquarter,
     FYear
FROM
$(vDataPath)\Master Fiscal Calendar.xlsx
(ooxml, embedded labels, table is Sheet1);

Now I would like to create a variable that always shows the current fiscal year, e.g. our fiscal year is starting in October so that already in October the variable needs to show the fiscal year 2014. Therefore I tried to create the following:

1. variable showing the date of today - this date I would like to map with the current fiscal year from the table CorporateCalendar

vtoday = Date (floor(Today ()));

2. I created a resident load and wanted to restrict the load only to the variable vtoday but this part is not working. My idea is that later I could substract e.g. -1 from the FYear to also get the previous year in chart formulas.
Todaytmp:
LOAD
OverallTransactionDate as DateToday,
FYear
Resident CorporateCalendar
where (DateToday=$(vtoday));

Screenshot from the Debugger:

I would appreciate your comments and ideas.

Best regards

Carolin

Re: resident load & where statement

Wed, 11 Dec 2013 10:22:16 GMT

Try this ..

where (OverallTransactionDate='$(vtoday)');

Regards,

Prabhu

Re: resident load & where statement

qlikpahadi07 — Wed, 11 Dec 2013 10:32:28 GMT

Hi Carolin,

you need to change your syntax from

Todaytmp:
LOAD
OverallTransactionDate as DateToday,
FYear
Resident CorporateCalendar
where (DateToday=$(vtoday));

Todaytmp:
LOAD
OverallTransactionDate as DateToday,
FYear
Resident CorporateCalendar
where (OverallTransactionDate =$(vtoday));

Actually Qlikview will never find the column DateToday as it is renamed so you have to use orignal column name which is OverallTransactionDate .

hope this works

cheers!!!

Re: resident load & where statement

jagan — Wed, 11 Dec 2013 10:48:59 GMT

Hi,

Give single quotes for the variable, since date is should be given as string.

odaytmp:
LOAD
OverallTransactionDate as DateToday,
FYear
Resident CorporateCalendar
where (DateToday='$(vtoday)');

HOpe this helps you.

Regards,

Jagan.

Re: resident load & where statement

carolin01 — Wed, 11 Dec 2013 15:49:53 GMT

Perfect - thanks to everybody for your support