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

Re: max date returns invalid expression

sunny_talwar — Tue, 31 Mar 2020 16:32:46 GMT

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

Re: max date returns invalid expression

dplr-rn — Tue, 31 Mar 2020 16:35:24 GMT

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?

Re: max date returns invalid expression

43918084 — Wed, 01 Apr 2020 07:36:02 GMT

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)

Re: max date returns invalid expression

43918084 — Wed, 08 Apr 2020 01:28:45 GMT

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

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