Skip navigation
Contact Us
5062 Views 10 Replies Latest reply: Jan 29, 2013 11:15 PM by Manojkumar Venkatachalam RSS
Naresh Neelam Newbie 4 posts since
May 31, 2011
Currently Being Moderated

Oct 21, 2011 3:55 AM

give the solution

Hi experts,

I have requriment like this,source table has >100000 records

 

serial_noIN_val_sr_no_rasonsr_for_1sr_for_2sr_for_3
ABC865YTENNNXXXXXNNNNNXNXXXNNNNNXXXNNNN
UYD8J8UUUXXXNNNXXXXXXXNXNXXXXXXXNNNNNN
AA856GB63XXNNNXXNNXXNNNXXXNNXXNNNXXNN
WQU8M6OHNNXXXNNXXNNXXXNNNXXNNXXXNNXNXN
SJUW9999UYSBXXXNNNXXXXXXXNNNXXNXXXXNNNXXXX
SKUJ96IJDJF8XXNNNXXNNXXNNNXXNNXXXNNXNXNXNN
QQQ890IIIMMXXXNNNXNXXXXXNNXXNXXXXXNNNXXNNN


I want output like this

 

serial_noIN_val_sr_no_rasonsr_for_1sr_for_2sr_for_3
ABC865YTEinvalid serial noNNNXXXXXNNNNNXNXXXNNNNNXXXNNNN
UYD8J8UUUinvalid serial noXXXNNNXXXXXXXNXNXXXXXXXNNNNNN
AA856GB63valid serial noXXNNNXXNNXXNNNXXXNNXXNNNXXNN
WQU8M6OHinvalid serial noNNXXXNNXXNNXXXNNNXXNNXXXNNXNXN
SJUW9999UYSBinvalid serial noXXXNNNXXXXXXXNNNXXNXXXXNNNXXXX
SKUJ96IJDJF8invalid serial noXXNNNXXNNXXNNNXXNNXXXNNXNXNXNN
QQQ890IIIMMinvalid serial noXXXNNNXNXXXXXNNXXNXXXXXNNNXXNNN

 

REQUIREMENT :

IF THE SERIAL_NO EXACTLY MATCH WITH ANY ONE OF THE OTHER SR_FORMATS(SR_FOR_1,

SR_FOR_2,SR_FOR_3) THEN  VALID SERIAL NO ELSE INVALID SERIAL NO

EX:   AA856GB63 THIS IS SAME AS THE FORMAT OF SR_FOR_1

please give me solution  ,  if u want then divide this table into any parts,all columns are varchars

 

Thanks,

Nari

  • Ram Krishna Newbie 12 posts since
    Sep 27, 2011
    Currently Being Moderated
    Oct 21, 2011 5:52 AM (in response to Naresh Neelam)
    give the solution

    hi,

    In expresion create a port and write the code and iif(sr_for_1=sr_for_2 and sr_for_1=sr_for_3,'VALID','INVALID')

    and connect this port to target.

     

    Please try and let me know.

  • sasi ramesh Master 1,110 posts since
    Oct 2, 2010
    Currently Being Moderated
    Oct 24, 2011 1:41 AM (in response to Naresh Neelam)
    give the solution

    Hi Naresh,

     

    Try the following,

     

    Use translate function or replacechr function to replace the alphabets with 'N' and Number with 'X'.Then compare the results with all the serialformat.

     

     

    In expression,Create the following ports in the order

     

    Alphabets=ABCD........Z --Enter all the alphabets

    Numbers=0123....9 --Enter all the numbers

    Alphanumeric = N

    Numeric = X

    ChangingAlphabets = Translate ( Serial_no, Alphabets,Alphanumeric) ---This will replace the alphabets with N

    Format_Final = Transalate( ChangingAlphabets,Numbers,Numeric) --This will replace the number with X

    Valid_Check =  IIF(  (Format_Final = sr_for_1 OR Format_Final = sr_for_2 OR Format_Final = sr_for_3), 'Valid Serial No', 'Invalid Serial No')

     

     

    Please try this and let me know the results

     

    FYI : You can use the replacestr function also ,but you need to provide all the alphabets and numberic in the REPLACESTR function.Refer syntax in the help docs

     

    Regards

    Sasiramesh

    • Ram Krishna Newbie 12 posts since
      Sep 27, 2011
      Currently Being Moderated
      Oct 24, 2011 10:50 AM (in response to sasi ramesh)
      Re: give the solution

      Hi Sasi,

      Can you please help me how  to use the TRANSLATE function in the xpression trnf/ powercenter, when i  use it in the expression it was showing error.(undefind function)

      • sasi ramesh Master 1,110 posts since
        Oct 2, 2010
        Currently Being Moderated
        Oct 25, 2011 1:28 AM (in response to Naresh Neelam)
        give the solution

        Hi Naresh,

         

        How to identify which are all the alphanumeric i.e 68 as per your above example.What is the logic and when should the numbers will be replaced with X instead of N.

         

        I have misplaced the replacing character in my previous post .Please use the below

         

        ChangingAlphabets=replacechr(1,serial_no,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','X')

        Format_Final=replacechr(1,ChangingAlphabets,'1234567890','N')

         

        for Above serial_no:986ADN68RNP

        ChangingAlphabets=986XXX68XXX

        Format_Final=NNNXXXNNXXX

         

        Required_Format = NNNXXXXXXXX

         

        I have given the required format above .You want to replace the numbers with X.Please corret me if i am wrong.

         

        Regards

        Sasiramesh

          • abdul azeem Newbie 1 posts since
            Sep 16, 2011
            Currently Being Moderated
            Nov 9, 2011 3:08 AM (in response to Naresh Neelam)
            Re: give the solution

            @Naresh : not sure about your requriement ,

             

            Convention : Alphabets : a-z A-Z  ;  Numerics : 0-9  ; Alphanumeric : a-z A-Z 0-9

             

            If your requirment is

            Case1)Alphabets[a-z A-Z] being represented by X and numerics[0-9] by N then the below expression should do,

             

            REG_REPLACE(REG_REPLACE(serial_no,'[a-zA-Z]','X'),'[0-9]','N')

             

            *note that first alphabets are converted to X and  not numerics as N indicating a number can be replaced by N character and  vice-versa.

            *as a work around you can use some other character #,* etc.,  to replace  each of X and N indicators. in this case you have to  translate the  sr_for_[1-3] using same conventions X ,N to other special  characters  [#,* etc.,] before comparision.

             

             

            Case2)Some numerics by N and alphanumeric as X

              In this case the X's are immaterial just have to validate that only numerics are present at the place where N is present ,

             

            we can use reduction to get both the serial_no and sr_for_[1-3] to a common notation for comparision,

            reduce the code present in sr_for_[1-3] in terms of X and N with X with 0 and and N as 1

            sr_for_[1-3]_val=TO_DECIMAL(REPLACECHR(1,REPLACECHR(1,sr_for_[1-3],'X','0'),'N','1'))

             

            and for the serial number mark the alphabets as 0

            serial_no_val=TO_DECIMAL(REG_REPLACE(serial_no,'[a-zA-Z]','0'))

             

            as weight of alphabets is lower than any number if  alphabets is present in place of a numeric it would imply  a overall  lower values, and hence..

             

            Now apply,

            IIF((serial_no_val >= sr_for_1_val AND length(sr_for_1)=length(serial_no)) OR

            (serial_no_val >= sr_for_2_val AND length(sr_for_2)=length(serial_no)) OR

            (serial_no_val >= sr_for_3_val AND length(sr_for_3)=length(serial_no)),

            'valid serial no','invalid serial no')

             

            As you mentioned number of records are high (>100k) try and splti the logic ,have filter before transformation.

  • Cezary Opacki Novice 47 posts since
    Mar 6, 2011
    Currently Being Moderated
    Nov 9, 2011 7:27 PM (in response to Naresh Neelam)
    Re: give the solution

    Hi,

     

    This is expression just for one pattern. The rest is easy.

     

     

    Expression.jpg

     

    REG=this is serial_no

    Pattern=this is sr_for1

    NewPatern= '('  ||  replacestr(true,replacestr(true,Patern,'X','[0-9,A-Z]'),'N','\d')  || ')'

    ifOK=iif(reg_match(REG,NewPattern),'IS VALID','IS NOT VALID')

     

     

    Regards

    Cezary Opacki

  • Manojkumar Venkatachalam Novice 82 posts since
    Dec 18, 2012
    Currently Being Moderated
    Jan 29, 2013 11:15 PM (in response to Naresh Neelam)
    Re: give the solution

    Hi Naresh ,

     

    Create an Expression Transformation and design following Ports :

     

    • SERIAL_NO , SR_FOR_1 , SR_FOR_2 , SR_FOR_3 - Input / Output Ports
    • V_SR_FOR_1 - Variable Port with Expression

     

            REG_REPLACE ( REG_REPLACE ( SR_FOR_1 , ' [0-9]? ' , ' N ' ) , ' \w? ', ' X ' )

     

    • V_SR_FOR_2 - Variable Port with Expression

     

            REG_REPLACE ( REG_REPLACE ( SR_FOR_2 , ' [0-9]? ' , ' N ' ) , ' \w? ', ' X ' )

     

    • V_SR_FOR_3 - Variable Port containing Expression

     

            REG_REPLACE ( REG_REPLACE ( SR_FOR_3 , ' [0-9]? ' , ' N ' ) , ' \w? ', ' X ' )

     

    • O_VALIDATION - Output Port containg the Expression

     

            IIF ( V_SR_FOR_1 = V_SR_FOR_2 AND V_SR_FOR_2 = V_SR_FOR_3 , ' VALID_SERIAL_NO ' , ' INVALID_SERIAL_NO ' )

More Like This

  • Retrieving data ...

Bookmarked By (0)