Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Luminary
Luminary

resident load & where statement

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:

screenshot.png

I would appreciate your comments and ideas.

Best regards

Carolin

1 Solution

Accepted Solutions
Highlighted
Not applicable

Try this ..

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

Regards,

Prabhu

View solution in original post

4 Replies
Highlighted
Not applicable

Try this ..

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

Regards,

Prabhu

View solution in original post

Highlighted
Specialist
Specialist

Hi Carolin,

you need to change your syntax from

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

to


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!!!


Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Luminary
Luminary

Perfect - thanks to everybody for your support