max date returns invalid expression

[43918084]

I have loaded 2 tables in script editor. One table with AcctgDate = 20191231,  The other table is AcctgDate = 20200131

[LYCM]:

Load

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

TradeID

From [lib//LY.csv]

Concatenate

Load

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

TradeID

From [lib//CY.csv]

NonConcatenate

Load *

max("ACCTG DATE (P0045)") as new

Resident [LYCM] ;

Drop Table [LYCM];

I want the New returns the latest AcctgDate which is 20200131.  Please advise how I can get this value.  Many thanks

[sunny_talwar]

If all you need is the 20200131, why are you loading 20191231 at all?

[dplr-rn]

you seem to be resident loading with other columns.

if there is other columns apart from the Max you need a group by.

What exactly are you trying to achieve?

[43918084]

Thank you very much.  I want to build one more field to return

1) Exist  if Global Trade Num (P0221) appear in both acctg Date (P0045) in 20200131 and 20191231

2) New if Global Trade Num (P0221) appear only in acctg Date (P0045)=20200131

2) Old if Global Trade Num (P0221) appear only in acctg Date (P0045)=20191231

But my script seems fail.  Hope you would not mind to give me some guidance

ACCTG DATE (P0045)	GLOBAL TRADE NUM (P0221)	SUM PROD CD (P0141)	Sum([FUNCTIONAL AMT(YTD)])
20200131	1096601H	500020	(1,778,256,338)
20191231	1096602H	500020	(1,919,695,278)
20200131	1096602H	500020	(1,908,026,044)
20191231	1154661H	500001	(1,946,500,000)
20200131	1154661H	500001	(1,942,325,000)
20191231	1160398H	500085	(6,368,948,000)

[43918084 · Accepted solution]

I created another load script and finally get it.

//*************************get latest month end date **************************************
left join
Load Max("ACCTG DATE (P0045)") as maxd
resident [LYCM];

//***********************************************************************************************