Maximum Date For Trigger on Sheet

[Anonymous]

I have a trigger on the Sheet level to automatically default to the maximum week.  This trigger is:

='='& Max(Date(Monday_Work_Week))

The expression appears in the Current Selection as seen above.  However, the maximum week is not selected (see image below).

I noticed that the date format is different.  The format in the Current Selection is 03-19-2018 for the Monday_Week_Work.  I date format for the Monday_Week_Work field is 2008-03-19.  Could this be why? If yes, how can I fix it.

[Anonymous]

I get the following message for using Date function:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date' is not a recognized built-in function name.

SQL select

Date(Monday_Work_Week, 'MM/DD/YYYY') as Monday_Work_Week

[sunny_talwar]

not in the sql my friend... in the load statement

[Anonymous]

[sunny_talwar]

Since you already have the name... you cannot repeat... may be try giving it a slightly different name

Date(Monday_Work_Week, 'MM/DD/YYYY') as Monday_Work_Week_New

and then after the load...

DROP Field Monday_Work_Week;

RENAME Field Monday_Work_Week_New to Monday_Work_Week;

[Anonymous]

The Monday_Work_Week_New has the format MON - MM/DD/YYYY.  I removed the "MON - " by using the following

convert(varchar(10), cast (Replace(c.StartOfWorkWeek, 'MON - ', '') as Date),101) as Monday_Work_Week

I put the above line into the Load section.  I get the following error message

:

[sunny_talwar]

We were going in the right direction quantran, why are you having a hard time understanding this.

You can use Qlik functions within Load... there is no convert function in load and hence it cannot be used in Qlik's load statement...

You can use SQL functions within Select... there is no date function in select and hence it cannot be used in SQL's select statement...

Data:

LOAD...,

     Date(Monday_Work_Week, 'MM/DD/YYYY') as Monday_Work_Week_New;

SQL SELECT ....

FROM ...

WHERE ...;

DROP Field Monday_Work_Week;

RENAME Field Monday_Work_Week_New to Monday_Work_Week;

Does it make sense my friend?

[Anonymous]

I was able to get it to work like you said.  Thank you for all your help!!!

[sunny_talwar]

Superb