topic Re: Count Total occurance in 2 concatenated tables in App Development

Count Total occurance in 2 concatenated tables

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

I need to concatenate below Table 1 and Table 2, then add calculated field "Total TradeID", and "Status"

But I keep getting 'Invalid expression' Error. Hope I can get some help.

Table1&2temp:

Load

Date(Date#([Reportdate], 'YYYYMMDD') ) AS [Reportdate],
[TradeID],
Date(Date#([MatDate], 'YYYYMMDD') ) AS [MatDate],
Date(Date#([TradeDate], 'YYYYMMDD') ) AS [TradeDate],
[Notional],
Count(DISTINCT TradeID) As Total_TradeID
RESIDENT [Table1&2] Group by Period, Reportdate, MatDate,TradeDate;
DROP TABLE [Table1&2];

Table1
Report date	TradeID	MatDate	TradeDate	Notional	Total TradeID	Status
20191231	ex1	20201230	20181030	100	4	Exist
20191231	ex1	20201230	20181030	100	4	Exist
20191231	mat1	20191230	20181030	10	1	matured
20191231	Term1	20201230	20181030	1	1	Exist
Table2
Report date	TradeID	MatDate	TradeDate	Notional
20200131	new1	20201230	20200101	50	1	Exist
20200131	new2	20201230	20200101	2	1	Exist
20200131	ex1	20201230	20181030	100	4	Exist
20200131	ex1	20201230	20181030	100	4	Exist

Re: Count Total occurance in 2 concatenated tables

Vegar — Tue, 03 Mar 2020 05:54:33 GMT

Table1&2temp:

Load

Date(Date#([Reportdate], 'YYYYMMDD') ) AS [Reportdate],
[TradeID],
Date(Date#([MatDate], 'YYYYMMDD') ) AS [MatDate],
Date(Date#([TradeDate], 'YYYYMMDD') ) AS [TradeDate],
[Notional]

RESIDENT [Table1&2];

Left join Load TradeID,

Count(TradeID) as TotalNoOfTradeIDs

Resident [Table1&2]

Group by TradeID;

Drop table [Table1&2];

Re: Count Total occurance in 2 concatenated tables

43918084 — Tue, 03 Mar 2020 06:30:01 GMT

Thank you so much. This solution really helps.

Sorry, I have one more question relating to the Status field. The status field is a calculated field based on the

Field TotalNoOfTradeIDs. I need to build formula like " If( TotlaNoOfTradeIDs = 1 and MatDate >Reportdate, 'Exist', 'Mature).

May I know how I can add the 'STatus' field into the Table1&2? Many thanks again