Error: Function WeekStart Doesnot Exist. In Where clause

[berryandcherry6]

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

[tamilarasu]

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]

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]

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]

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]

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 · Accepted solution]

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');