topic Re: Max(Date) returns invalid expression in App Development

Max(Date) returns invalid expression

43918084 — Mon, 20 Dec 2021 21:32:42 GMT

I have a table with field

Date(Date#("ACCTG DATE (P0045)",'yyyyMMdd'),'YYYYMMDD') as "ACCTG DATE (P0045)"

and I want to have a field to return latest date.

I used Max("ACCTG DATE (P0045)") and get invalid expression

Hope I can get some guidance

Re: Max(Date) returns invalid expression

agigliotti — Thu, 02 Apr 2020 09:38:15 GMT

maybe this:

=Max([ACCTG DATE (P0045)])

I hope it can help.

Re: Max(Date) returns invalid expression

43918084 — Thu, 02 Apr 2020 09:41:34 GMT

Thank you very much for your help. Unfortunately it does not work. I still get invalid expression

Re: Max(Date) returns invalid expression

agigliotti — Thu, 02 Apr 2020 09:46:10 GMT

what is your field name?
could you post a screenshot?

Re: Max(Date) returns invalid expression

43918084 — Thu, 02 Apr 2020 09:48:38 GMT

Thank you very much again. My field name is "ACCTG DATE (P0045)". The double quote is included as field name.

Please find below my script which has not include the Max formula:-

Load
Distinct "GLOBAL TRADE NUM (P0221)",
"ACCTG DATE (P0045)",

"MATURITY DATE (P0288)",
"TRADE DATE (P0561)",

Rowno()-1 as row,
recno() as current,
Peek('GLOBAL TRADE NUM (P0221)') as PrevDeal,
peek('ACCTG DATE (P0045)') as PreAcctgdate,
if(Peek('GLOBAL TRADE NUM (P0221)') = "GLOBAL TRADE NUM (P0221)",'y','n') as check1,

if(Peek('ACCTG DATE (P0045)')=date(date#('20191231','yyyyMMdd'),'yyyyMMdd'),2,1)as check2,
if(Peek('GLOBAL TRADE NUM (P0221)') = "GLOBAL TRADE NUM (P0221)" and Peek('ACCTG DATE (P0045)')=date(date#('20191231','yyyyMMdd'),'yyyyMMdd'),'exist',
if(Peek('GLOBAL TRADE NUM (P0221)') <> "GLOBAL TRADE NUM (P0221)" and Peek('ACCTG DATE (P0045)')=date(date#('20191231','yyyyMMdd'),'yyyyMMdd'), 'old',
if(Peek('GLOBAL TRADE NUM (P0221)') <> "GLOBAL TRADE NUM (P0221)"and Peek('ACCTG DATE (P0045)')<>date(date#('20191231','yyyyMMdd'),'yyyyMMdd'), 'new', 2)))as check3

RESIDENT [LYCM]
order by "GLOBAL TRADE NUM (P0221)","ACCTG DATE (P0045)" ;

Drop Table [LYCM];

Re: Max(Date) returns invalid expression

Mauritz_SA — Thu, 02 Apr 2020 09:50:51 GMT

Hi @43918084

Change yyyyMMdd to YYYYMMDD in your script.

Regards,

Mauritz

Re: Max(Date) returns invalid expression

43918084 — Thu, 02 Apr 2020 11:35:20 GMT

Thank you very much for your advice. I have replaced all yyyyMMdd to YYYYMMDD. But I still get the invalid expression.

May I know if max("ACCTG DATE (P0045)") is the correct syntax? The double quote is in the original field

Re: Max(Date) returns invalid expression

Mauritz_SA — Thu, 02 Apr 2020 11:46:16 GMT

Hi @43918084

Sorry, I didn't see your response. As far as I know max("ACCTG DATE (P0045)") is the same as max([ACCTG DATE (P0045)]) so both will work. Can you paste a sample ACCTG DATE (P0045) value so that we can see its format?

Regards,

Mauritz

Re: Max(Date) returns invalid expression

43918084 — Thu, 02 Apr 2020 12:09:53 GMT

Thank you very much indeed.

Please find below a sample of the data. I really appreciate your help

GLOBAL TRADE NUM (P0221)	ACCTG DATE (P0045)
503232H	20191231
503232H	20200131
503237H	20191231
503237H	20200131
960509H	20191231
960509H	20200131
1096601H	20200131
1096602H	20191231

Re: Max(Date) returns invalid expression

agigliotti — Thu, 02 Apr 2020 12:47:44 GMT

in script:

load
date#([ACCTG DATE (P0045)], 'YYYYMMDD') as [ACCTG DATE (P0045)];
LOAD * INLINE [
GLOBAL TRADE NUM (P0221), ACCTG DATE (P0045)
503232H, 20200131
503237H, 20191231
503237H, 20200131
960509H, 20191231
960509H, 20200131
1096601H, 20200131
1096602H, 20191231
];

in UI:

=max([ACCTG DATE (P0045)])

Re: Max(Date) returns invalid expression

43918084 — Thu, 02 Apr 2020 12:57:50 GMT

Thank you very much for your immediate help.

I have changed the script to Date#("ACCTG DATE (P0045)",'YYYYMMDD') as "ACCTG DATE (P0045)"

But when I apply max([ACCTG DATE (P0045]) I still have invalid expression unfortunately

Re: Max(Date) returns invalid expression

lorenzoconforti — Thu, 02 Apr 2020 13:15:42 GMT

In which chart object are you getting invalid expression? Can you share a screenshot?

Re: Max(Date) returns invalid expression

Mauritz_SA — Thu, 02 Apr 2020 15:26:37 GMT

Hi @43918084

You need another bracket max([ACCTG DATE (P0045)]).

If this doesn't work then please send a screenshot as @lorenzoconforti mentioned.

Regards,

Mauritz

Re: Max(Date) returns invalid expression

43918084 — Thu, 09 Apr 2020 12:34:15 GMT

Thank you very much Mr Agigliotti for your patience to help me many times.

I finally did an extra load and get the problem solved

left join
Load Max("ACCTG DATE (P0045)") as maxd
resident [LYCM];