Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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];
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