Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Date format Problem in scripting

hi Everyone,

     I wrote this expression ,i m getting Invalid expression error.Any suggestions would be greatly appreciated

if(Date(Month,'MM-DD-YYYY')>=Date(Max(Month(Month)),'MM-DD-YYYY'),'Yes','N0') as Flag,

PFA

Thanks in Advance

13 Replies
MVP
MVP

Re: Date format Problem in scripting

could you explain when the Flag should be Yes?

EDIT: flag max month?

Tab2:

Load *,

//if(Date(Month,'MM-DD-YYYY')>=Date(Max(Month(Month)),'MM-DD-YYYY'),'Yes','N0') as Flag,

if(isnull(Peek(Month)),'Yes','No') as Flag,

      Month(Month) as LogicMonth,

      Year(Month) as Logicyear

resident Tab

order by Month desc;

Not applicable

Re: Date format Problem in scripting

Divya i am not sure about the fix but i have few questions which might help you. in the excel you provided there is no date part at all and i did't understand what date you  are trying to extract. since Qlikview treats month with dual values we can assume the case that it take the number of the month in the right hand part but in left hand it is not getting the same sort of value i believe.  i think you can check on month instead of date.

cspencer3
Contributor II

Re: Date format Problem in scripting

Is it because your Month field is not in a valid date format or number that could be interpreted into a date? Therefore the date function cannot handle it.

konstantin
Contributor III

Re: Date format Problem in scripting

You cannot use MAX() function in the expression you provided,

Make a variable with Maximum month, like

Let vMaxMonth = peek('Month',-1);

and use the following:

if(Date(Month,'MM-DD-YYYY')>=Date($(vMaxMonth),'MM-DD-YYYY'),'Yes','N0') as Flag,

Re: Date format Problem in scripting

Hi,

Load *,if(Date(Month,'MM-DD-YYYY')>=Date(Max(Month(Month)),'MM-DD-YYYY'),'Yes','N0') as Flag,

      Month(Month) as LogicMonth,

      Year(Month) as Logicyear resident Tab;

The reason, why this Load fails, is the missing group by statement.

Using an aggregation function (here max()), you have to group by all fields used without aggregation functions in the same load.

So while


Load *,if(Date(Month,'MM-DD-YYYY')>=Date(Max(Month(Month)),'MM-DD-YYYY'),'Yes','N0') as Flag,

      Month(Month) as LogicMonth,

      Year(Month) as Logicyear

Resident Tab

Group By Amount, Month;

at least will load without error, the question remains, what you would like to achieve, because comparing dates with numerical values like 41275 (representing 1/1/2013) with months (having values from 1 to 12) does not make much sense. The flag will always be 'yes' because all relevant date values are greater than month values.

hope this helps

regards

Marco

Not applicable

Re: Date format Problem in scripting

Hi Massimo,

             when the month is greater than current month.  The flag should be yes

Regards,

Divya

Not applicable

Re: Date format Problem in scripting

hi ,

Actually in my database it is date format only (1/12014).

For sample i have created only with Month an year.

I need data Greater than the current month.Pls suggest me

Regards,

Divya

MVP
MVP

Re: Date format Problem in scripting

if(Month > monthend(today()),'Yes','N0') as Flag

to test with your data

if(Month > monthend(today()-300),'Yes','N0') as Flag,

Not applicable

Re: Date format Problem in scripting

Hi,

    Thanks for your Helpful information.I have used the same code in my Application.I am getting Invalid expression Error.Please suggest me. .

LOAD *,if(Date(cmdbci_Date1,'DD-MM-YYYY')>=Date(Max(Month(cmdbci_Date1)),'DD-MM-YYYY'),'Yes','N0') as Flag,

       Month(cmdbci_sys_created_on) as CIMonth,

       Year(cmdbci_sys_created_on)  as Year

       Resident CMDB

       Group by Tempcmdbci_name,cmdbci_Date1;

Thanks in Advance

Community Browser