34 Replies Latest reply: Dec 20, 2017 5:31 AM by omar bensalem RSS

    Search field value in another field (occurance)

    omar bensalem

      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 !

        • Re: Search field value in another field (occurance)
          Sunny Talwar

          May be you can use MapSubstring - script function ‒ QlikView to extract the right value from the second table?

          • Re: Search field value in another field (occurance)
            omar bensalem

            Hi stalwar1, with the way things are

             

            for example:

             

            FxFA | FX | EUR is associated with FxFA | FX | EUR and FxFA | FX | EUR | 02


            BUT

            when in one hand I have:

            FxFA | EUR


            and in the other hand :

            FxFA | FX | EUR

            and

            FxFA | FXSWAP | EUR

             

            these does not get associated.. even though, the FxFA | EUR does exist in both of the second values in table 2 (but the order is not respected..do u see it?)

             

            Is there a way to associate this :

            FxFA | EUR

            to :

            FxFA | FX | EUR

            and

            FxFA | FXSWAP | EUR



            Thanks a ton Sun !

              • Re: Search field value in another field (occurance)
                omar bensalem

                I thought about using subfield to seperate FxFA | EUR and see if both (at the same time) exists in

                FxFA | FX | EUR

                and

                FxFA | FXSWAP | EUR;


                if yes (both exist): associate :

                FxFA | EUR to

                FxFA | FX | EUR

                and

                FxFA | FXSWAP | EUR

                 

                if not: do not associate..

                 

                Easy said than done..

                  • Re: Search field value in another field (occurance)
                    omar bensalem

                    This is how I've done:

                    table1:

                    load *, SubField(Key,'|',1) as NewKey,SubField(Key,'|',2) as NewKey2;

                    LOAD * INLINE [

                        ID, Key, Value1, Value2

                        2, USD|70648, Valuex, valuey

                    ];

                     

                    Mapping:

                    Mapping

                    LOAD DISTINCT NewKey,

                    '\' & NewKey & '/'

                    Resident table1;

                    Mapping2:

                    Mapping

                    LOAD DISTINCT NewKey2,

                    '\' & NewKey2 & '/'

                    Resident table1;

                     

                    table2:

                    LOAD *,

                    TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

                    TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

                    LOAD * INLINE [

                        key, otherField

                        XXX|70648|02, 300

                        AA|70648|05|USD, 500

                        DD|70648|USD, 200

                        70648|USD|1919|EST, 150

                    ];

                     

                    result: (right one) :

                    Capture.PNG

                     

                     

                    prblem 1 :

                    It's static; what if Key was composed of more than 2 values...

                    problem 2 :

                    Synthetic keys generation

                    Capture.PNG


                    How could/should I optimize this?

                    Thanks ! stalwar1

                      • Re: Search field value in another field (occurance)
                        omar bensalem

                        This works only where there are 3 values constructing the first key in table1:

                         

                        table:

                        LOAD * INLINE [

                            ID, Key, Value1, Value2

                            3, 70648,50,20

                          2, USD|70648, Valuex, valuey

                            4, USD|70648|xx, Valuex, valuey //works for this one !

                            1, fxFA | EUR, 20,30

                        ];

                        NoConcatenate

                        table1:

                        Load *, trim(SubField(Key,'|',1)) as NewKey, trim(SubField(Key,'|',2)) as NewKey2, trim(SubField(Key,'|',3)) as NewKey3 Resident table where len(KeepChar(Key,'|'))<>0 ;

                        Drop Table table;

                         

                        Mapping:

                        Mapping

                        LOAD DISTINCT NewKey,

                        '\' & NewKey & '/'

                        Resident table1;

                         

                        Mapping2:

                        Mapping

                        LOAD DISTINCT NewKey2,

                        '\' & NewKey2 & '/'

                        Resident table1;

                         

                        Mapping3:

                        Mapping

                        LOAD DISTINCT NewKey3,

                        '\' & NewKey3 & '/'

                        Resident table1;

                         

                        Join (table1) // to eliminate the synthetic key; maybe it's doing sthing wrong..

                        table2:

                        LOAD *,

                        TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

                        TextBetween(MapSubString('Mapping3', key), '\', '/') as NewKey3,

                        TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

                        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

                        ];

                         

                        final:

                        NoConcatenate Load * Resident table1 where  NewKey&NewKey2&NewKey3<>NewKey and NewKey&NewKey2&NewKey3<>NewKey2  and NewKey&NewKey2&NewKey3<>NewKey3

                        ;

                        Drop Table table1;

                         

                        Capture.PNG

                         

                        NOT CORRECT

                        Capture.PNG

                        ------------------------------------------------------------------------------------------------------------------------------

                        This works only where there are 2 values constructing the first key in table1:

                         

                        table:

                        LOAD * INLINE [

                            ID, Key, Value1, Value2

                            3, 70648,50,20

                          2, USD|70648, Valuex, valuey //works for this one !

                            4, USD|70648|xx, Valuex, valuey

                            1, fxFA | EUR, 20,30 //works for this one !

                        ];

                        NoConcatenate

                        table1:

                        Load *, trim(SubField(Key,'|',1)) as NewKey, trim(SubField(Key,'|',2)) as NewKey2 Resident table where len(KeepChar(Key,'|'))<>0 ;

                        Drop Table table;

                         

                        Mapping:

                        Mapping

                        LOAD DISTINCT NewKey,

                        '\' & NewKey & '/'

                        Resident table1;

                         

                        Mapping2:

                        Mapping

                        LOAD DISTINCT NewKey2,

                        '\' & NewKey2 & '/'

                        Resident table1;

                         

                         

                         

                        Join (table1) // to eliminate the synthetic key; maybe it's doing sthing wrong..

                        table2:

                        LOAD *,

                        TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

                         

                        TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

                        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

                        ];

                         

                        final:

                        NoConcatenate Load * Resident table1 where  NewKey&NewKey2<>NewKey and NewKey&NewKey2<>NewKey2 

                        ;

                        Drop Table table1;

                         

                        Capture.PNG

                        Capture.PNG

                        Not correct :

                        Capture.PNG

                         

                        -----------------------------------------------------------------------------------------------------------------------

                         

                        and finally,

                        This works only where there are only one value constructing the first key in table1:

                        table:

                        LOAD * INLINE [

                            ID, Key, Value1, Value2

                           3, 70648,50,20 //works for this one !

                          2, USD|70648, Valuex, valuey

                            4, USD|70648|xx, Valuex, valuey

                            1, fxFA | EUR, 20,30

                        ];

                        NoConcatenate

                        table1:

                        Load *, Key as NewKey,Key as NewKey2 Resident table where len(KeepChar(Key,'|'))=0 ;

                        Drop Table table;

                         

                        Mapping:

                        Mapping

                        LOAD DISTINCT NewKey,

                        '\' & NewKey & '/'

                        Resident table1;

                         

                        Mapping2:

                        Mapping

                        LOAD DISTINCT NewKey2,

                        '\' & NewKey2 & '/'

                        Resident table1;

                         

                         

                         

                        Join (table1) // to eliminate the synthetic key; maybe it's doing sthing wrong..

                        table2:

                        LOAD *,

                        TextBetween(MapSubString('Mapping', key), '\', '/') as NewKey,

                         

                        TextBetween(MapSubString('Mapping2', key), '\', '/') as NewKey2;

                        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

                        ];

                         

                        final:

                        NoConcatenate Load * Resident table1 where  NewKey&NewKey2<>NewKey and NewKey&NewKey2<>NewKey2 

                        ;

                        Drop Table table1;

                        Capture.PNG

                         

                         

                        WHAT SHOULD I DO TO COMBINE ALL THESE 3 CASES? is there an alternative to this?

                        Please help... stalwar1

                  • Re: Search field value in another field (occurance)
                    Sunny Talwar

                    I tried to run your script and I get this association

                     

                    Capture.PNG

                     

                    Is this right that the key USD|70648|xx is associated with id 2, 3, & 4? Is this what your goal is that if it finds multiple matches, you want to assign all of them?

                      • Re: Search field value in another field (occurance)
                        omar bensalem

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

                        check your linkedin inbox

                          • Re: Search field value in another field (occurance)
                            Sunny Talwar

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

                              • Re: Search field value in another field (occurance)
                                omar bensalem

                                Can u take a look at it once home?

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

                                    • Re: Search field value in another field (occurance)
                                      omar bensalem

                                      u're my 'last' hope..

                                        • Re: Search field value in another field (occurance)
                                          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

                                            • Re: Search field value in another field (occurance)
                                              omar bensalem

                                              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;

                                • Re: Search field value in another field (occurance)
                                  Sunny Talwar

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

                                  • Re: Search field value in another field (occurance)
                                    Marco Wedel

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

                                    • Re: Search field value in another field (occurance)
                                      Marco Wedel

                                      Hi,

                                       

                                      maybe this could be one solution?

                                       

                                      QlikCommunity_Thread_285126_Pic1.JPG

                                       

                                      QlikCommunity_Thread_285126_Pic2.JPG

                                       

                                      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

                                        • Re: Search field value in another field (occurance)
                                          omar bensalem

                                          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.

                                          Capture.PNG

                                           

                                          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..

                                          Capture.PNG

                                           

                                          Any help would so muuch needed please !

                                        • Re: Search field value in another field (occurance)
                                          omar bensalem

                                          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;

                                          • Re: Search field value in another field (occurance)
                                            omar bensalem

                                            AND marcowedel

                                            If u have any other way/solution/idea to do this in an easier/better way, I'll be more then happy to know it !

                                            Thanks