Search field value in another field (occurance)

[OmarBenSalem]

Hi all ( stalwar1‌ , swuehl‌

I have 2 table:

1) table1 have a field called Key as follow:

Key :

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

It has values such as :

70648|USD that's associated to an ID=2 for example !

Now the Key in the second table contains values like this:

XXX|70648|USD|02

AA|70648|USD|05

DD|70648|USD

70648|USD|1919|EST

so; the ID=2 is obviously not associated with :

XXX|70648|USD|02

AA|70648|USD|05

DD|70648|USD

70648|USD|1919|EST

The question is: how to do to associate these values since Key from the first table exists in the second?

Thanks a ton !

[OmarBenSalem]

Yes; it works; but when I try it with the actual tables; it does not..

check your linkedin inbox

[sunny_talwar]

I cannot download from LinkedIn at work.... Don't have access to cloud drives, social media, gmail etc

[OmarBenSalem]

Can u take a look at it once home?

PLEASE.. I'm really desperate and very sorry to bother u...

[sunny_talwar]

Yes, I will

[OmarBenSalem]

u're my 'last' hope..

[sunny_talwar]

There is another one who can decipher this and let's look for his help as well. marcowedel‌ please help Omar if you have time.

Best,

Sunny

[OmarBenSalem]

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;

[sunny_talwar]

Just need some time to work on this.... still working on it, but it may take some time

[OmarBenSalem]

Take all uur time sunny ! It would be a life saving !

[MarcoWedel]

As you closed your thread I guess you already found the answer to your question?