4 Replies Latest reply: Feb 10, 2012 6:58 AM by Henric Cronström RSS

    read and change fieldnames based on expression during load

      i was searching in the reference guide for some expression that reads the fieldname like other expressions read the content of the field.

       

      f.e. thre are filds like  building1, building2, building3

      the goal is, to change the format from

       

      building1, value

      23, temp

      80, moist

       

      to

       

      building, b_number, value

      23, 1, temp

      80, 1, moist

       

      is it possible to read these like

      right(anyfildname,1) to get the numbers (1,2,3 ec.) ?

       

      if so, it could be possible to used in an expression like this:

      LET b_number = right_fieldnames(1);

      OR

      LET b_number = IF (left_fieldname(5)='build', right_fieldnames(1));

       

      is something like this possible ?

        • read and change fieldnames based on expression during load
          Erich Shiino

          Hi, Alexander

          You can achieve this using cross table.

           

          I wrote a sample script as an example. In this piece of code, I wrote a table using inline and store, but you can start from the second step, using crosstable on your original tables:

           

           

          //Creation of Test Table

          Test:

          LOAD * INLINE [

              STUDY, TYPE, BUIDING1, BUILDING2, BUILDING3

              1, A, 200, 300, 400

              1, B, 100, 50, 20

              1, C, 15, 90, 400

              2, A, 15, 32, 200

              2, B, 200, 100, 150

              2, C, 50, 100, 70

          ];

           

          Store Test into Test.qvd;

          drop table Test;

          //

          //QVDs will be read to simulate a source file

          //Cross table could be done in the original Load Inline

          tempBuildings:

          CrossTable(tempBuilding, Value, 2)

          LOAD STUDY,

               TYPE,

               BUIDING1,

               BUILDING2,

               BUILDING3

          FROM

          [Test.qvd]

          (qvd);

           

          //At this point tempBuildings has the Fields: STUDY, TYPE, tempBuilding and Value

           

          Building:

          Load STUDY,

               TYPE, Right(tempBuilding,1) as Building,

               Value

          resident tempBuildings;

          drop table tempBuildings;

           

           

          Hope this helps,

           

          Erich

          • Re: read and change fieldnames based on expression during load
            Henric Cronström

            Yes it is possible. See attachment. You should look in the help at the Crosstable statement and the string functions.

              • read and change fieldnames based on expression during load

                Im sorry, this was a missunderstanding.

                the example i provided was a bit misleading.

                 

                im not looking for a crosstable statement or a string function. this wouldnt be the problem (there are tons of examples for this here)

                 

                im looking for something like

                 

                "Right(tempBuilding,1) as Building,"

                 

                but the source should be the field name (tempBuilding) and not the content.

                 

                something that just reads the number from the name of the field

                 

                LET Numfield= Right(field7,1)

                 

                like

                Right(test_table,1) , so its gets the last digit (or char) of every fieldname in "test_table"

                 

                im sorry for the missunderstanding.