13 Replies Latest reply: Jun 30, 2015 2:15 AM by Federico Velasco RSS

    Where field problem

    Federico Velasco

      Hi all,

       

      I have a question:

       

      In this sample script:

       

      DATA:

      LOAD

        *

      Inline [

      Campo1, Valor1

      AAA, 111

      BBB,222

      ]

      ;

       

      DATA2:

      LOAD

        Campo1,

        Valor1

      Resident DATA

      Where Campo2 = 'AAA'

      ;

       

      DROP Table DATA;

       

      Can I put a IF condition in WHERE to let the two names (Campo1 and Campo2)?

      The problem is when loading some excel files in a loop, the field name is changing.

       

      Thank you.

        • Re: Where field problem
          Giuseppe Gallina

          Hi,

           

          can you explain better what do you mean "some excel files in a loop, the field name is changing".

          Or attach sample data?

          • Re: Where field problem
            Sunny Talwar

            Where did Campo2 came from? I do not see that in your original DATA table?? You cannot refer a field in your resident load if they are not present in your original field.

             

            Best,

            Sunny

              • Re: Where field problem
                Federico Velasco

                Hi,

                 

                It was a "sample".

                I do loop and I load some excel like:

                For Each vFile in filelist *


                     DATA:

                     LOAD

                          Max(Floor([Creada]))          as "Max_Fecha"

                     FROM [$(vFile)]

                     Where NOT [Campo1] like 'Generado*';


                Next vFile;


                The problem is when in excel Campo1 is renamed as Campo2.

                Have I do a preload of all excel renaming the field name to Campo1?


                Sorry for my poor explanation.


                  • Re: Where field problem
                    Stefan Wühl

                    Instead of referencing the field using embedded field names, you can use the table columns, like A, B ,C or  @1, @2 ... field references (but depending on the Excel file version also).

                    You need to skip the row with the embedded lables then. I would suggest to use the table wizard to create a propert LOAD statement.

                     

                    This will only work if your columns in the Excel may have a changing header, but same semantics (i.e. first column is always Date field etc.).

                    • Re: Where field problem
                      Peter Cammaert

                      If you can detect the correct name of the column-to-be-used in the WHERE clause from the actual filename in $(vFile), you could set a second variable e.g. vColumn to the name of the correct column to use. For example:

                       

                      For Each vFile in filelist *


                          IF check-if-vFile-meets-certain-conditions THEN

                            SET vColumn = Campo1;

                          ELSE

                            SET vColumn = Campo2;

                          END IF;


                          DATA:

                          LOAD  $(vColumn) AS Campo1,

                                Max(Floor([Creada]))          as "Max_Fecha"

                          FROM [$(vFile)]

                          Where NOT [$(vColumn)] like 'Generado*';


                      Next vFile;



                  • Re: Where field problem
                    Harshal Patil

                    Hi,

                     

                    Please provide some sample data for better explanation,

                     

                    Thanks