Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
maybe this could be one solution?
table1:
LOAD * INLINE [
ID, Key, Value1, Value2
3, 70648, 50, 20
2, USD|70648, Valuex, valuey
4, USD|70648|xx, Valuex, valuey
1, fxFA | EUR, 20, 30
];
table2:
LOAD * INLINE [
key, otherField
XXX|70648|02, 300
fxFA | EUR, 500
EUR|fxFA|200, 200
300|fxFA, 10
USD|70648|xx, 50
xx|300|USD|70648, 100
EUR|500|fxFA, 300
AA|70648|05|USD, 500
DD|70648|USD, 200
70648|USD|1919|EST, 150
];
tabKey:
LOAD Key,
Text(Trim(SubField(Key,'|'))) as SubKey
Resident table1;
mapSubKey:
Mapping
LOAD Distinct
SubKey,
'@start@'&SubKey&'@end@'
Resident tabKey;
Left Join (table1)
LOAD Key,
AutoNumber(Concat(DISTINCT SubKey,'|'),'Key') as %Key
Resident tabKey
Group By Key;
DROP Table tabKey;
Left Join (table2)
LOAD key,
AutoNumber(Concat(DISTINCT SubKey,'|'),'Key') as %Key
Group By key;
LOAD key,
TextBetween(MapSubString('mapSubKey',key),'@start@','@end@',IterNo()) as SubKey
Resident table2
While IterNo()<=SubStringCount(MapSubString('mapSubKey',key),'@start@');
hope this helps
regards
Marco
Hi Marco and thank u very much for ur time ! I really appreciate it
70648 exists in more than one Key; it should be associated with every Key that contains it.
Same thing for : USD|70648
the 2 values exist (at the same time) in USD|70648|xx and xx|300|USD|70648
and thus should also be associated with those 2 Keys..
Any help would so muuch needed please !
stalwar1 !
I made it woooooooooooooooooooooooooork 
Thresholds:
load * ,if(len(Trim(THR.Product_Code))=0 and Len(Trim(THR.Currency))=0,'|'&THR.CDR_Standardized&'|',
if(len(Trim(THR.Product_Code))=0 and Len(Trim(THR.Currency))<>0 ,'|'&THR.CDR_Standardized&'|'&THR.Currency&'|',
if(len(Trim(THR.Product_Code))<>0 and Len(Trim(THR.Currency))=0 ,'|'&THR.CDR_Standardized&'|'&THR.Product_Code&'|',
('|'&THR.CDR_Standardized&'|'&THR.Product_Code&'|'&THR.Currency&'|')))) as Key ;
LOAD
[ThresholdUniqueID] AS [ThresholdUniqueID],
[Threshold_ID] AS [Threshold_ID],
upper([Trading_Desk]) AS [THR.Trading_Desk],
upper([CDR_Standardized]) AS [THR.CDR_Standardized],
upper([Product_Code]) AS [THR.Product_Code],
upper([Currency]) AS [THR.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;
Thresholds_keywoords0:
NoConcatenate
LOAD ThresholdUniqueID,trim(text(subfield(Key,'|'))) as kw
resident Thresholds;
Thresholds_keywoords:
NoConcatenate
LOAD ThresholdUniqueID,kw
resident Thresholds_keywoords0
where isnull(kw)=0 and kw<>'' and trim(kw)<>'';
drop table Thresholds_keywoords0;
left join(Thresholds)
LOAD ThresholdUniqueID,count(DISTINCT kw) as keycount,count(DISTINCT kw) as countmatchthr
resident Thresholds_keywoords
//where isnull(kw)=0 and trim(kw)<>''
group by ThresholdUniqueID;
//******************************** Notionals table **********************************************
Not:
LOaD
AsOf_Date,
Tree_ID,
Entity,
CDR,
Book,
Portfolio,
Branch,
Folder,
Product_ID,
upper([Trading_Desk]) AS [Trading_Desk],
upper([CDR_Standardized]) AS [CDR_Standardized],
upper([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
rowno() as NotId,
*,
if(len(Trim(Product_Code))=0 and Len(Trim(Currency))=0,'|'&CDR_Standardized&'|',
if(len(Trim(Product_Code))=0 and Len(Trim(Currency))<>0 ,'|'&CDR_Standardized&'|'&Currency&'|',
if(len(Trim(Product_Code))<>0 and Len(Trim(Currency))=0 ,'|'&CDR_Standardized&'|'&Product_Code&'|',
('|'&CDR_Standardized&'|'&Product_Code&'|'&Currency&'|')))) as key
Resident Not;
Drop Table Not;
left join(Notionals)
LOAD ThresholdUniqueID,// as idfk,
text(kw) as kw2//,countmatchthr
resident Thresholds_keywoords;
table3_prefilter:
NoConcatenate
LOAD *,
if(index('|' & key,'|'&kw2&'|')>0,1,0) as ismatch
resident Notionals;
mainfact0:
NoConcatenate
LOAD distinct NotId,ThresholdUniqueID,kw2,1 as ismatch Resident table3_prefilter where ismatch=1;
drop table table3_prefilter;
left join(mainfact0)
LOAD ThresholdUniqueID,countmatchthr
Resident Thresholds;
mainfact1:
LOAD NotId,ThresholdUniqueID,countmatchthr,count(ismatch) as countmatchnot
resident mainfact0
group by NotId,ThresholdUniqueID,countmatchthr;
drop table mainfact0;
mainfact:
NoConcatenate
LOAD NotId,ThresholdUniqueID resident mainfact1 where countmatchthr=countmatchnot;
drop table mainfact1;
drop fields ThresholdUniqueID from Notionals;
