Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
maxgro
MVP
MVP

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
Author

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
Creator II
Creator II

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.

Anonymous
Not applicable
Author

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,

MarcoWedel

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
Author

Hi Massimo,

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

Regards,

Divya

Not applicable
Author

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

maxgro
MVP
MVP

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

to test with your data

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

Not applicable
Author

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