Here's the script I'm using and that's not working fine (not associating correctly) ; if this could help :
//*************************** Threshold table **************************************
LIB CONNECT TO 'Get Thresholds';
RestConnectorMasterTable:
SQL SELECT
"ThresholdUniqueID",
"Threshold_ID",
"Trading_Desk",
"CDR_Standardized",
"Product_Code",
"Currency",
"Daily_Variation",
"Monthly_Variation",
"Yearly_Variation",
"Aggregation_Type",
"Valid_From",
"Valid_To",
"Active",
"Creation_Date",
"Creation_User",
"Modification_Date",
"Modification_User"
FROM JSON (wrap on) "root";
Thresholds:
LOAD *,trim(text(subfield(Key,'|'))) as kw ;
load * ,
if(len(Trim(Product_Code))=0 and Len(Trim(Currency))=0,text( CDR_Standardized),
if(len(Trim(Product_Code))=0 and Len(Trim(Currency))<>0 ,text(CDR_Standardized&'|'&Currency),
if(len(Trim(Product_Code))<>0 and Len(Trim(Currency))=0 ,text(CDR_Standardized&'|'&Product_Code),
text(CDR_Standardized&'|'&Product_Code&'|'&Currency)))) as Key
;
LOAD
[ThresholdUniqueID] AS [ThresholdUniqueID],
[Threshold_ID] AS [Threshold_ID],
upper(text([Trading_Desk])) AS [Trading_Desk],
upper(text([CDR_Standardized])) AS [CDR_Standardized],
upper(text([Product_Code])) AS [Product_Code],
upper([Currency]) AS [Currency],
[Daily_Variation] AS [Daily_Variation],
[Monthly_Variation] AS [Monthly_Variation],
[Yearly_Variation] AS [Yearly_Variation],
[Aggregation_Type] AS [Aggregation_Type]
RESIDENT RestConnectorMasterTable
Where Date(Today(1)) >=Date([Valid_From]) and Date(Today(1))<=Date([Valid_To]) and Capitalize([Active])='True'
;
DROP TABLE RestConnectorMasterTable;
drop Fields Product_Code,Currency,CDR_Standardized,Trading_Desk from Thresholds;
left join
LOAD Key,count(DISTINCT kw) as keycount
resident Thresholds
group by Key;
//******************************** Notionals table **********************************************
Not:
LOaD
AsOf_Date,
Tree_ID,
Entity,
CDR,
Book,
Portfolio,
Branch,
Folder,
Product_ID,
upper(text([Trading_Desk])) AS [Trading_Desk],
upper(text([CDR_Standardized])) AS [CDR_Standardized],
upper(text([Product_Code])) as [Product_Code],
upper([Currency]) AS [Currency],
Current_Notional_Net,
Current_Notional_Gross
, Extraction_Date
FROM [lib://Medium Data/notional.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq) where len(Trim(CDR_Standardized))<>0 ;
Join(Not)
LOAD
AsOf_Date
, Base_Currency as Currency,
Pivot_Currency,
FX_Rate
FROM [lib://Medium Data/fxRate.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)
;
Notionals:
NoConcatenate
load
*,
if(len(Trim(Product_Code))=0 and Len(Trim(Currency))=0,text(CDR_Standardized),
if(len(Trim(Product_Code))=0 and Len(Trim(Currency))<>0 ,text(CDR_Standardized&'|'&Currency),
if(len(Trim(Product_Code))<>0 and Len(Trim(Currency))=0 ,text(CDR_Standardized&'|'&Product_Code),
text(CDR_Standardized&'|'&Product_Code&'|'&Currency)))) as key
Resident Not;
Drop Table Not;
drop Fields Product_Code,Currency,CDR_Standardized,Trading_Desk from Notionals;
left join(Notionals)
LOAD ThresholdUniqueID,// as idfk,
kw as kw2 resident Thresholds;
table3:
NoConcatenate
LOAD *,if(WildMatch(key,'*'&kw2&'*'),1,0) as ismatch
resident Notionals;
drop Table Notionals;
left join
load key,ThresholdUniqueID,sum(ismatch) as countmatch
Resident table3
group by key,ThresholdUniqueID;
inner join(table3)
LOAD ThresholdUniqueID,keycount as countmatch
resident Thresholds;
Notionals:
NoConcatenate
LOAD distinct key,
ThresholdUniqueID,
AsOf_Date,
Tree_ID,
Entity,
CDR,
Book,
Portfolio,
Branch,
Folder,
Current_Notional_Net,
Current_Notional_Gross
,
Extraction_Date
Resident table3;
drop Table table3;
table1bis:
NoConcatenate
load distinct ThresholdUniqueID,
Key,
[Threshold_ID],
[Daily_Variation],
[Monthly_Variation],
[Yearly_Variation]
,
[Aggregation_Type]
resident Thresholds;
drop table Thresholds;
rename table table1bis to Thresholds;
Exit Script;
