6 Replies Latest reply: Jan 8, 2014 6:37 PM by Rodrigo Giner RSS

    Help: Generate Table from a long string variable with Autogenerate

    Rodrigo Giner

      Hi, I have a long string with lots of values with this format:

       

      LET vData = '28-Mick   -Smith     -X|30-John  -Robert    - -|43-Sonia   -Stone   -X';

       

      So "-" separate fields value and "|" separate Entries and what I want is obtain a tabla like this using Autogenerate.

       

      AgeNameLastNameActve
      28MickSmithX
      30JohnRobert
      43SoniaStoneX

       

      I know the quantity of entries and fields (iRows and iFields). I manage to get it work but hardcoding stuff, I cant manage to make it work in a generic form without hardcoding the number of fields in the subfield statement

       

      For i=1 to $(iRows)

        LET row = SubField('$(vData)','|',i);

        LOAD 

        SubField('$(row)','-',1) AS Age,

        SubField('$(row)','-',2) AS Name,

        SubField('$(row)','-',3) AS LastName,

        SubField('$(row)','-',4) AS Active

        AutoGenerate(1);

      Next

       

      Can someone help me pls

       

      Thanks

        • Re: Help: Generate Table from a long string variable with Autogenerate
          Nicole Smith

          How about the following?  (Also attached.)

          Set vData = '28-Mick   -Smith -X|30-John  -Robert - -|43-Sonia   -Stone   -X';

          Let vRows = SubStringCount(vData,'|')+1;

           

          for i=1 to $(vRows)

              Data:

              load SubField(SubField('$(vData)', '|', $(i)), '-', 1) as Age,

               SubField(SubField('$(vData)', '|', $(i)), '-', 2) as Name,
               SubField(SubField('$(vData)', '|', $(i)), '-', 3) as LastName,
               SubField(SubField('$(vData)', '|', $(i)), '-', 4) as Active

              AutoGenerate 1;

          next

          • Re: Help: Generate Table from a long string variable with Autogenerate
            Srikanth P

            You can try with IterNo() like below:

             

            Set vData = '28-Mick  -Smith-X|30-John  -Robert- -|43-Sonia  -Stone  -X';

             

            LOAD Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',1)) AS Age,

                  Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',2)) AS FirstName ,
                  Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',3)) AS LastName ,
                  Trim(SubField(SubField('$(vData)' , '|',IterNo()),'-',4))AS Active

            AutoGenerate 1

            While IterNo() <= SubStringCount('$(vData)','|')+1;

             

            Please find the attached qvw for reference.

            • Re: Help: Generate Table from a long string variable with Autogenerate
              Rob Wunderlich

              I think a preceding load like this would be easiest. A for loop is not necessary.

               

              LET vData = '28-Mick  -Smith    -X|30-John  -Robert    - -|43-Sonia  -Stone  -X';

               

              Data:

              LOAD

                trim(SubField(Entry,'-',1)) as Age

                ,trim(SubField(Entry,'-',2)) as Name

                ,trim(SubField(Entry,'-',3)) as LastName

                ,trim(SubField(Entry,'-',4)) as Active

              ;

              LOAD

                SubField('$(vData)','|') as Entry

              AutoGenerate 1

              ;

               

              -Rob

              • Re: Help: Generate Table from a long string variable with Autogenerate
                Rodrigo Giner

                Sorry, perhaps I expressed in the wrong way... English isn't my native language. What I want is to avoid enumerate the "fields number" (marked in red)

                 

                For i=1 to $(iRows)

                  LET row = SubField('$(vData)','|',i);

                  LOAD

                  SubField('$(row)','-',1) AS Field1,

                  SubField('$(row)','-',2) AS Field2,

                  SubField('$(row)','-',3) AS Field3,

                  SubField('$(row)','-',4) AS Field4

                  AutoGenerate(1);

                Next

                 

                In this example I have 4 fields but I need to be generic, not always will be 4 fields. Im working with macros to retrieve data from SAP Tables and depending of the Table the field varies. I know the max field number (variable iField) but what I need is to "iterate" them to avoid "hard coding" the fields numbers.

                 

                The fields allways will be separated by "-" and the entries with "|" but the number of fields and row are not allways the same.

                 

                So I have to make a script Load thats is able to read

                 

                LET vData = '28-Mick   -Smith     -X|30-John  -Robert    - -|43-Sonia   -Stone   -X';

                Or for example

                LET vData = '20140108-Sunny|20140107-Cold|20140107-Snow';

                 

                For example the For iterate the iRows wharever the amount... I need that but also for the fields, is there a way to acomplish this ? in another words I want to "emulate" the logic behind having two nested For (one for the rows and another for the fields).

                 

                Thx you for you help.

                 

                Greetings

                  • Re: Help: Generate Table from a long string variable with Autogenerate
                    Rob Wunderlich

                    You can use the two parameter version of subfield() to handle a variable number of values.

                    (Note in the code below, I had to add "Y" at the end of Robert. There is some bug/problem with the entry ending with "-").

                     

                    LET vData = '28-Mick   -Smith     -X|30-John  -Robert    - Y|43-Sonia   -Stone   -X';

                    Data:

                    LOAD

                      *

                      ,AutoNumber(RecNo(),RecId) as FieldNumber

                    ;

                    LOAD

                      RecNo() as RecId,

                      trim(SubField(Entry,'-')) as Field

                    ;

                    LOAD

                      SubField('$(vData)','|') as Entry

                    AutoGenerate 1

                    ;

                     

                    I assume you'll have some kind of ordinal field mapping that will let you use this output.

                    RecId FieldNumber FieldValue
                    1128
                    12Mick
                    13Smith
                    14X
                    2130
                    22John
                    23Robert
                    24Y
                    3143
                    32Sonia
                    33Stone
                    34X

                     

                    For example:

                    Final:

                    LOAD RecId, FieldValue as Age

                    RESIDENT Data

                    WHERE FieldNumber=1

                    ;

                    JOIN (Final)

                    LOAD RecId, FieldValue as Name

                    RESIDENT Data

                    WHERE FieldNumber=2

                    ;

                     

                    -Rob