2 Replies Latest reply: May 30, 2018 9:16 AM by Marcus Sommer RSS

    Expression Block in Field List

    peter grillo

      This mess works:

       

      LOAD

              "Date",

              if(Index(Description, 'CHECKCARD') = 1,       

                  if(FindOneOf(Right(Description,Len(Description)-15),'0123456789') > 1,

                  Left(Right(Description,Len(Description)-15), FindOneOf(Right(Description,Len(Description)-15),'0123456789')-1),

                      Right(Description,Len(Description)-15)),

                  Description)

              As Description,

              Money(-1 * Amount) As Amount

          FROM [lib://Downloads/stmt (3).csv]

          (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

          Where Num(Amount) < 0

       

      but what I'd really like is a way to get an expression block in the field list.  Something like:

       

           LOAD

                "Date",

                Magic = {

                     set x=2;

                     set y=2;

                     x+y;

                },

                Amount

       

       

      Is there a way to do that?  If not can you enhance the Data Load Editor.

       

      Thanks.

        • Re: Expression Block in Field List
          peter grillo

          Or:

           

               LOAD

                    "Date",

                    Begin

                         set x=2;

                         set y=2;

                         (x+y) As Magic;

                    End

                    },

                    Amount

          • Re: Expression Block in Field List
            Marcus Sommer

            I don't believe that something in this way is possible but I think it could be simplified. Depending of the field-content and your checking other string-functions like subfield/textbetween/keepchar might be more suitable as your used left/right-approach. If not could in each case a Preceding Load be useful to simplify the logic, maybe in this way:

             

                    LOAD *, if(Y, if(Z, Left(Right(Description,X), Z-1, Description) as DescriptionNew;

            LOAD *, FindOneOf(Right(Description,X),'0123456789') as Z;

            LOAD

                    "Date",

                    Money(-1 * Amount) As Amount,

                    Description,

                    Len(Description)-15 as X,

                    if(Index(Description, 'CHECKCARD') = 1, 1, 0) as Y

                FROM [lib://Downloads/stmt (3).csv]

                (txt, codepage is 28591, embedded labels, delimiter is ',', msq)

                Where Num(Amount) < 0

             

            There might be some small error in this snippet but I think you will understand the logic of preceeding-load.

             

            - Marcus