Max(Date) returns invalid expression

[43918084]

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

[agigliotti]

maybe this:

=Max([ACCTG DATE (P0045)]) 

I hope it can help.

[43918084]

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

[agigliotti]

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

[43918084]

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];

[Mauritz_SA]

Hi @43918084 

Change yyyyMMdd to YYYYMMDD in your script.

Regards,

Mauritz

[43918084]

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

[Mauritz_SA]

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

[43918084]

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

[agigliotti]

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)])