4 Replies Latest reply: Aug 17, 2017 2:00 PM by D.R.NEVILLE DHAMSIRI DHAMSIRI RSS

    Extrcting PRD_code

    D.R.NEVILLE DHAMSIRI DHAMSIRI

      Hi,

      Any body please help me to extract PRD_CODE from this data loading.The PRD_CODE is in the policy_number field which are (1C,1D,1B,3F,1F,1X). Since the length of the policy numbers are different, right function, I applied did not work for all the numbers due to the length issue of the policy numbers.But there should be some way out to write this in the script just below the policy number.Hope some body will help me to resolve this.

      Directory;

      LOAD POLICY_NUMBER,

           REF_NUMBER,

           PREMIUM

      FROM

      [..\POL_NUMBER.xlsx]

      (ooxml, embedded labels, table is Sheet1);

        • Re: Extrcting PRD_code
          Felip Drechsler

          Hi Neville,

           

          See the bellow code to see if this is what you need (i also attached a QVW but it may be possible that your using Sense).

           

          sample:

          Load * Inline

          [

          POLICY_NUMBER,REF_NUMBER,PREMIUM

          01RT1C00071,145,1

          01RT1C000699,115,1

          01RT1C000711,116,2

          02RT1D000862,120,3

          04rT1B001008,121,4

          99RT3F002514,132,5

          RA00141F0001087,135,6

          RA00141X0000973,125,7

          ];

           

          codes:

          Load * Inline

          [

          Code

          1C

          1D

          1B

          3F

          1F

          1X

          ];

           

          for i = 0 to NoOfRows('sample')-1

          let vPolicy = peek('POLICY_NUMBER',$(i),'sample');

           

          for each vCode in FieldValueList('Code')

          trace $(vCode);

          if (index('$(vPolicy)','$(vCode)')>0) then

          exit for;

          end if;

          next;

           

          data:

          Load

          '$(vPolicy)' as Policy,

          '$(vCode)' as Code

          AutoGenerate(1);

          next i;

           

          drop table codes,sample;

           

          Felipe.

          • Re: Extrcting PRD_code
            D.R.NEVILLE DHAMSIRI DHAMSIRI

            Dear Felip,

             

            Since there are large number of policy records,use of inline feature looks difficult to me.Would you mine explaining me a simple method I could use like  right or left function that we use in Excel!

             

            Thanks for your reply  very fast

              • Re: Extrcting PRD_code
                Felip Drechsler

                For the left and right type of solution, you would need to check different indexes for each text you want to search (1C,1D,1B,3F,1F,1X) since you don't have any type of delimiter or any other type of defined formula (something like, the Policy code starts at the 5th character of the string).

                You could do that with a nested if, something like:

                sample:

                LOAD

                     if

                     (

                          index(POLICY_NUMBER,'1C')>0,

                          '1C',

                          if

                          (

                               index(POLICY_NUMBER,'1D')>0,

                               '1D',

                               ... // And so on, for every text you want to search

                     ) as POLICY_CODE,

                     REF_NUMBER,

                     PREMIUM

                FROM

                [..\POL_NUMBER.xlsx]

                (ooxml, embedded labels, table is Sheet1);


                For my solution, I just used the inlines for example purposes, you can add your own tables there, something like this

                 

                sample:

                LOAD POLICY_NUMBER,

                     REF_NUMBER,

                     PREMIUM

                FROM

                [..\POL_NUMBER.xlsx]

                (ooxml, embedded labels, table is Sheet1);

                 

                codes:

                Load

                     Code

                From [Excel file where the different codes you have are]

                 

                // This tests all the rows of the sample table, where the policy numbers are

                for i = 0 to NoOfRows('sample')-1

                     // gets the policy number of the current row

                     let vPolicy = peek('POLICY_NUMBER',$(i),'sample');

                 

                     // tests for each individual line of the codes table, to check wich code it is

                     for each vCode in FieldValueList('Code')

                          // verifies if there's an ocurrence of the currently code in the string from the data table, if so, exits for

                          if (index('$(vPolicy)','$(vCode)')>0) then

                               exit for;

                          end if;

                     next;

                 

                     // loads the correspondent resulting table for each row

                     data:

                     Load

                          '$(vPolicy)' as Policy,

                          '$(vCode)' as Code

                     AutoGenerate(1);

                next i;

                 

                drop table codes,sample;

              • Re: Extrcting PRD_code
                D.R.NEVILLE DHAMSIRI DHAMSIRI

                Thanks for the solution.It worked.