1 2 Previous Next 17 Replies Latest reply on Dec 10, 2020 12:41 AM by Nico Heinze

    LIKE Clause in Expression transformation

    Saurabh Shrivastava Guru

      Hello All,

       

      I have a requirement where in I have to check for below condition(Provided by business)

      upper(UniBody/Frame Damage)=’YES’ OR

      upper(Operable)=’NO’ OR

      TRIM(TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(General Remarks),'[^A-Z0-9 ]'),'( ){2,}',' ')) || ' ' || TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(Announced Conditions),'[^A-Z0-9 ]',''),'( ){2,}',' '))) like

      '%AIRBAG%'

      ,'%CANT DRIVE%'

      ,'%CANT START%'

      ,'%DAMAGE%'

      ,'%DMG%

      ,'%ENGINE%'

      ,'%FLOOD%'

      ,'%FRAME%'

      ,'%HAIL%'

      ,'%INOP%'

      ,'%LEMON%'

      ,'%NO DRIVE%'

      ,'%NO START%'

      ,'%NON DRIVABLE%'

      ,'%NON START%'

      ,'%NOT DRIVABLE%'

      ,'%ODOMETER%'

      ,'%RECOVERY%'

      ,'%SALV%'

      ,'%STOLEN%'

      ,'%STRUCTURAL%'

      ,'%THEFT%'

      ,'%TMU%'

      ,'%TRANSMISSION%'

      ,'%UNIBODY%'

      ,'%WATER DAMAGE%'

       

      So for this i created an output port in an Expression Transformation and specified below

      UPPER(i_UniBody_Frame_Damage)='YES' OR

      UPPER(i_Operable)='NO' OR

      TRIM(TRIM(REG_REPLACE(REG_REPLACE(UPPER(i_General_Remarks),'[^A-Z0-9 ]'),'( ){2,}',' ')) || ' ' || TRIM(REG_REPLACE(REG_REPLACE(UPPER(i_Announced_Conditions),'[^A-Z0-9 ]',''),'( ){2,}',' '))) like

      ('%AIRBAG%',

      '%CANT DRIVE%',

      '%CANT START%',

      '%DAMAGE%',

      '%DMG%',

      '%ENGINE%',

      '%FLOOD%',

      '%FRAME%',

      '%HAIL%',

      '%INOP%',

      '%LEMON%',

      '%NO DRIVE%',

      '%NO START%',

      '%NON DRIVABLE%',

      '%NON START%',

      '%NOT DRIVABLE%',

      '%ODOMETER%',

      '%RECOVERY%',

      '%SALV%',

      '%STOLEN%',

      '%STRUCTURAL%',

      '%THEFT%',

      '%TMU%',

      '%TRANSMISSION%',

      '%UNIBODY%',

      '%WATER DAMAGE%'

      )

       

      But this gives an error upon validating the expression

      Can someone please help me on this ?

        • 1. Re: LIKE Clause in Expression transformation
          David Lopez Cruz Guru

          I would suggest you divide the expression and add it section by section and check it.

          For instance you can start with:

          UPPER(i_UniBody_Frame_Damage)='YES'

           

          And after confirm this one:

          UPPER(i_UniBody_Frame_Damage)='YES' OR

          UPPER(i_Operable)='NO'

           

          Until you find the condition which is failing.

          • 2. Re: LIKE Clause in Expression transformation
            David Lopez Cruz Guru

            Maybe you are missing here the new character string for REG_REPLACE?

            REG_REPLACE(UPPER(i_General_Remarks),'[^A-Z0-9 ]')

            REG_REPLACE(UPPER(i_General_Remarks),'[^A-Z0-9 ]',XXX)

            • 3. Re: LIKE Clause in Expression transformation
              Saurabh Shrivastava Guru

              I suspect that the issue is with LIKE Clause and we are specifying multiple conditions inside like clause.

              Not sure If like clause with wildcard characters works in Informatica.

              • 4. Re: LIKE Clause in Expression transformation
                Smitha HC Guru

                Hi Saurabh,

                 

                In Informatica we do not have function called LIKE, Alternatively you can use InStr() instead of SubStr().

                 

                Thanks,
                Smitha

                • 5. Re: LIKE Clause in Expression transformation
                  Saurabh Shrivastava Guru

                  Yes, I found that LIKE clause is not available in Informatica.

                  Now converting this trim(regexp() clause into INSTR format is another challenge. I guess I need to go through the details of regular expressions first in order to convert into INSTR.

                   

                  • 6. Re: LIKE Clause in Expression transformation
                    Smitha HC Guru

                    Hi Saurabh,

                     

                    You can refer below guide.

                    INSTR

                     

                    Thanks,

                    Smitha

                    • 7. Re: LIKE Clause in Expression transformation
                      Nico Heinze Guru

                      You should split up the work into several distinct variable ports, that makes life far easier.

                       

                      Start with two variable ports of type String holding the UPPER() of the trimmed "general remarks" resp. "announced conditions" texts.

                       

                      Next you concatenate these two strings into one more variable port (which you may, for example, name v_full_text).

                       

                      Now set up 26 variable ports of type Integer named v_pos_airbag, v_pos_cant_drive, v_pos_cant_start, and so on down to v_pos_water_damage with these expressions:

                      v_pos_airbag:

                          INSTR( v_full_text, 'AIRBAG')

                      v_pos_cant_drive:

                          INSTR( v_full_text, 'CANT DRIVE')

                      v_pos_cant_start:

                          INSTR( v_full_text, 'CANT START')

                      and so on down to v_pos_water_damage:

                          INSTR( v_full_text, 'WATER DAMAGE')

                       

                      Finally you can set up a variable port v_condition_met (type Integer) with this expression:

                      UPPER(UniBody_Frame_Damage)=’YES’ OR

                      UPPER(Operable)=’NO’ OR

                      v_pos_airbag > 0 OR

                      v_pos_cant_drive > 0 OR

                      v_pos_cant_start > 0 OR

                      v_pos_damage > 0 OR

                      v_pos_dmg% > 0 OR

                      v_pos_engine > 0 OR

                      v_pos_flood > 0 OR

                      v_pos_frame > 0 OR

                      v_pos_hail > 0 OR

                      v_pos_inop > 0 OR

                      v_pos_lemon > 0 OR

                      v_pos_no_drive > 0 OR

                      v_pos_no_start > 0 OR

                      v_pos_non_drivable > 0 OR

                      v_pos_non_start > 0 OR

                      v_pos_not_drivable > 0 OR

                      v_pos_odometer > 0 OR

                      v_pos_recovery > 0 OR

                      v_pos_salv > 0 OR

                      v_pos_stolen > 0 OR

                      v_pos_structural > 0 OR

                      v_pos_theft > 0 OR

                      v_pos_tmu > 0 OR

                      v_pos_transmission > 0 OR

                      v_pos_unibody > 0 OR

                      v_pos_water_damage > 0

                       

                      Just make sure when copying this text that the port names are correct.

                       

                      Regards,

                      Nico

                      • 8. Re: LIKE Clause in Expression transformation
                        Saurabh Shrivastava Guru

                        Thanks Nico, This looks pretty easy.

                        BTW can you please help me out in understanding that what exactly below REGEX statements are doing ...?

                        TRIM(TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(General Remarks),'[^A-Z0-9 ]'),'( ){2,}',' ')) || ' ' || TRIM(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(Announced Conditions),'[^A-Z0-9 ]',''),'( ){2,}',' ')))...

                         

                        I mean do we need to check for them as well....?

                        • 9. Re: LIKE Clause in Expression transformation
                          Nico Heinze Guru

                          Not sure, that's one reason why I haven't touched this topic earlier.

                           

                          First of all they definitely are NOT written in PowerCenter syntax (which is a slightly stripped-down version of Perl 5 compatible regular expressions), so I can only guess that they should mean. Your business people better give you a plain-text explanation instead of such nonsense; text is easier to understand for "us" techies than such pseudo-technical expressions which may or may not be valid regular expressions (in which language???).

                           

                          As far as I understand them, they perform these steps:

                          1. "general remarks" is turned into uppercase only.
                          2. "general remarks" is stripped off anything which is neither a letter A-Z nor a digit 0-9 nor a blank space.
                          3. What this '( ){2,}',' ' shall do, I have no idea.
                          4. "announced conditions" is turned into uppercase only.
                          5. "announced conditions" is stripped off anything which is neither a letter A-Z nor a digit 0-9 nor a blank space.
                          6. Again, no idea what this '( ){2,}',' ' means.
                          7. The result from step #3 plus one blank space plus the result from step #6 are concatenated into one string.
                          8. Finally this string is examined for any of those 26 special texts.

                           

                          Again, my guess, please ask your business people for a plain-text explanation of what they want. It is no pleasure for me to "guess" what business people mean, I prefer to get clean and concise explanations and definitions, not such pseudo programming code.

                           

                          Regards,

                          Nico

                          • 10. Re: LIKE Clause in Expression transformation
                            Nico Heinze Guru

                            Some more thinking about the regular expression revealed that probably this "funny stuff" '( ){2,}',' ' shall mean the following:

                            Each time there are two or more blank spaces (e.g. in "this  is   text  with    at  least   two  spaces"), these multiple blank spaces shall be replaced by one single space.

                            This is something that should (in theory, I've never tried it myself) work in PowerCenter as well.

                             

                            Please note that the respective function in PowerCenter is named REG_REPLACE() and not REGEXP_REPLACE().

                            May I suggest that you read about the regular expression functions in the Transformation Language Guide for PowerCenter, this paragraph will explain all these details.

                             

                            Finally please note that it's sufficient to first do this (assign this value to some variable port of type String):

                                Upper( LTrim( RTrim( i_General_Remarks || ' ' || i_Announced_Conditions)))

                            Then you can apply the REG_REPLACE() function twice on this concatenated string (once for the removal of everything but A-Z, 0-9, and blank spaces, and once for the replacement of two or more blank spaces by one single blank space). This is more efficient and faster than applying all these functions on both ports separately and then only concatenate the results of these operations.

                             

                            Regards,

                            Nico

                            • 11. Re: LIKE Clause in Expression transformation
                              Saurabh Shrivastava Guru

                              This is what business people replied to me for that REGEXP

                               

                              "You would remove any special characters from these fields and compare to the list of strings in the requirement document (ie: from AIRBAG till WATER DAMAGE)."

                               

                              Can someone help in arranging this in regular expressions ?

                              • 12. Re: LIKE Clause in Expression transformation
                                Nico Heinze Guru

                                Frankly spoken I wouldn't do that. It's not worth the effort. The solution that I have depicted is faster, safer to use, and easier to extend than the use of regular expressions.

                                And please trust me, I don't write this because I would dislike regular expressions in general (that's not true), it is just my experience that my approach as depicted above is easier and safer than trying to do this with regular expressions; building all this via regex is a waste of performance and computing power and time. It's so much slower and more CPU consuming than my approach. I really wouldn't do this because it doesn't make any real sense.

                                 

                                Anyway. If you insist of doing this via regex, then I suggest you read the paragraphs about REG_EXTRACT(), REG_REPLACE(), and REG_MATCH() in the Transformation Language guide for PowerCenter, there you will find all the information you need.

                                 

                                Regards,

                                Nico

                                • 13. Re: LIKE Clause in Expression transformation
                                  Saurabh Shrivastava Guru

                                  Hello Nico,

                                   

                                  Thanks a lot for your valuable suggestions.

                                  Below is what I have done -->

                                  created a variable port (v_Regex) of type string and its value is as below

                                  LTRIM(RTRIM(REG_REPLACE(REG_REPLACE(UPPER(i_General_Remarks),'[^A-Z0-9 ]',''),'\s+',' ')) ) || ' ' ||  LTRIM(RTRIM(REG_REPLACE(REG_REPLACE(UPPER(i_Announced_Conditions),'[^A-Z0-9 ]',''),'\s+',' ')) )

                                   

                                  then created an output port of type string, which would determine whether the area is broken or clean(condition to be checked) and this output port has value:

                                   

                                  IIF(UPPER(i_UniBody_Frame_Damage)='YES' OR

                                  UPPER(i_Operable)='NO' OR

                                  INSTR(v_Regex,'AIRBAG') > 0 OR

                                  INSTR(v_Regex,'CANT DRIVE') > 0 OR

                                  INSTR(v_Regex,'CANT START') > 0 OR

                                  INSTR(v_Regex,'DAMAGE') > 0 OR

                                  INSTR(v_Regex,'DMG') > 0 OR

                                  INSTR(v_Regex,'ENGINE') > 0 OR

                                  INSTR(v_Regex,'FLOOD') > 0 OR

                                  INSTR(v_Regex,'FRAME') > 0 OR

                                  INSTR(v_Regex,'HAIL') > 0 OR

                                  INSTR(v_Regex,'INOP') > 0 OR

                                  INSTR(v_Regex,'LEMON') > 0 OR

                                  INSTR(v_Regex,'NO DRIVE') > 0 OR

                                  INSTR(v_Regex,'NO START') > 0 OR

                                  INSTR(v_Regex,'NON DRIVABLE') > 0 OR

                                  INSTR(v_Regex,'NON START') > 0 OR

                                  INSTR(v_Regex,'NOT DRIVABLE') > 0 OR

                                  INSTR(v_Regex,'ODOMETER') > 0 OR

                                  INSTR(v_Regex,'RECOVERY') > 0 OR

                                  INSTR(v_Regex,'SALV') > 0 OR

                                  INSTR(v_Regex,'STOLEN') > 0 OR

                                  INSTR(v_Regex,'STRUCTURAL') > 0 OR

                                  INSTR(v_Regex,'THEFT') > 0 OR

                                  INSTR(v_Regex,'TMU') > 0 OR

                                  INSTR(v_Regex,'TRANSMISSION') > 0 OR

                                  INSTR(v_Regex,'UNIBODY') > 0 OR

                                  INSTR(v_Regex,'WATER DAMAGE') > 0,

                                  'BROKEN', 'CLEAN')

                                  • 14. Re: LIKE Clause in Expression transformation
                                    Nico Heinze Guru

                                    Looks correct to me.

                                     

                                    Cheers,

                                    Nico

                                    1 2 Previous Next