Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Error: Function WeekStart Doesnot Exist. In Where clause

Hi,

I am executing below script in Qliksense, to get data for previous week in table.

LOAD `help_request_id`,    

    status,

    Date(date_created,'DD-MM-YYYY') as Format_Date;

SQL SELECT `help_request_id`,

    status,

    `date_created`

FROM sdasa.HELPREQUESTS WHERE date_created >= Date(WeekStart( Today()-7)) and date_created <= Date(WeekEnd( Today()-7));

But above Script throws Error: Function WeekStart Doesnot Exist.

Regards,

Supriya

1 Solution

Accepted Solutions
sunny_talwar

Then try like this:

Let vWeekStart = Date(WeekStart(Today()-7), 'YYYY-MM-DD hh:mm:ss');

Let vWeekEnd  = Date(WeekEnd(Today()-7), 'YYYY-MM-DD hh:mm:ss');

View solution in original post

10 Replies
tamilarasu
Champion
Champion

Hi,

Try this,

Let vWeekStart = Date(WeekStart( Today()-7));

Let vWeekEnd = Date(WeekEnd( Today()-7));

LOAD `help_request_id`,   

    status,

    Date(date_created,'DD-MM-YYYY') as Format_Date;

SQL SELECT `help_request_id`,

    status,

    `date_created`

FROM sdasa.HELPREQUESTS WHERE date_created >= '$(vWeekStart)' and date_created <= '$(vWeekEnd)';

berryandcherry6
Creator II
Creator II
Author

Hi,

Thanks for reply.

But i am fetching no rows. Because date created cannot be >= '$(vWeekStart)' and '$(vWeekEnd)'; at the same time.

sunny_talwar

May be try this slight modification to Tamil's response

Let vWeekStart = Date(WeekStart(Today()-7), 'DD-MMM-YYYY');

Let vWeekEnd  = Date(WeekEnd(Today()-7), 'DD-MMM-YYYY');

berryandcherry6
Creator II
Creator II
Author

Hi Sunny,

Its showing all Datas. where condition is not validating.

sunny_talwar

Its pulling all the data? regardless of the where statement, you see the same number of rows getting pulled?

berryandcherry6
Creator II
Creator II
Author

Yes, its pulling all records.

sunny_talwar

I think you need to talk to you DB guys and ask them what format of date is used as a default.... Once you figure the format, test run the query outside QlikView to make sure you know how many rows of data to expect. Next, you come back to QlikView and set your variables with the date format needed

Let vWeekStart = Date(WeekStart(Today()-7), 'DateFormatNeeded');

Let vWeekEnd  = Date(WeekEnd(Today()-7), 'DateFormatNeeded');

TRACE $(vWeekStart);

TRACE $(vWeekEnd);

Run just the above to make sure that the Date format is exactly the same as the one you ran in your SQL query outside QlikView.

Once all of the above is met, I don't see why your query won't work. If it still doesn't work, then I have no idea how else to help you and you might need to get in touch with a SQL guru and not a QlikView expert

Best,

Sunny

berryandcherry6
Creator II
Creator II
Author

Hi Sunny,

Format of date_created is in this format '2016-12-19 00:00:00'

If i execute below query in Mysql i get 14 rows.

select * from HELPREQUESTS where (date_created BETWEEN  '2016-12-19 00:00:00' and '2016-12-25 23:59:59') ;

sunny_talwar

Then try like this:

Let vWeekStart = Date(WeekStart(Today()-7), 'YYYY-MM-DD hh:mm:ss');

Let vWeekEnd  = Date(WeekEnd(Today()-7), 'YYYY-MM-DD hh:mm:ss');