topic Re: Date format Problem in scripting in QlikView

Date format Problem in scripting

Tue, 16 Sep 2014 19:32:07 GMT

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

Re: Date format Problem in scripting

maxgro — Tue, 16 Sep 2014 19:56:27 GMT

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;

Re: Date format Problem in scripting

Tue, 16 Sep 2014 20:06:25 GMT

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.

Re: Date format Problem in scripting

cspencer3 — Tue, 16 Sep 2014 20:09:12 GMT

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.

Re: Date format Problem in scripting

Anonymous — Tue, 16 Sep 2014 20:10:11 GMT

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

MarcoWedel — Tue, 16 Sep 2014 21:59:27 GMT

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

Re: Date format Problem in scripting

Wed, 17 Sep 2014 04:01:35 GMT

Hi Massimo,

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

Regards,

Divya

Re: Date format Problem in scripting

Wed, 17 Sep 2014 04:05:18 GMT

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

Re: Date format Problem in scripting

maxgro — Wed, 17 Sep 2014 07:45:35 GMT

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

to test with your data

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

Re: Date format Problem in scripting

Wed, 17 Sep 2014 12:45:39 GMT

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

Re: Date format Problem in scripting

MarcoWedel — Wed, 17 Sep 2014 22:05:29 GMT

Hi,

might be the same.

At least the field 'cmdbci_sys_created_on' has to be included in the group by statement.

Depending on the other fields of your table CMDB, you load with '*', it might be even more to include in the group by.

hope this helps

regards

Marco

Re: Date format Problem in scripting

Thu, 18 Sep 2014 06:19:15 GMT

hi,

i m using Below expression:

=Sum({$<Date = {"<=$(=MonthEnd(AddMonths(Max(Date),-1)))"}>}Amount)

It is Working fine .when i select on Date field it is fine .

If i use Month(Date) and i select on particular month it is giving output as 0.

Pls suggest me.

Thanks in Advance

Re: Date format Problem in scripting

Anonymous — Thu, 18 Sep 2014 06:28:27 GMT

ekkada pani chesthunnaru meeru sister

Re: Date format Problem in scripting

MarcoWedel — Thu, 18 Sep 2014 07:26:42 GMT

please close this thread if your question is answered and open new threads for different topics.

thanks

regards

Marco