3 Replies Latest reply: Dec 6, 2013 7:11 AM by Friedrich Hofmann RSS

    Flexibilize a fieldname in a LOAD

    Friedrich Hofmann

      Hi,

       

      there is one qvw that loads a nr. of Excel tables with an asterisk in the FROM part.

      <=> In one of those tables, the name of one field usually (not necessarily always) comes slightly different

      => Usually a colleague of mine fixes that in the morning, but that's risky - he sometimes, though seldom, forgets and the LOAD fails.

       

      => Can I somehow dynamically query the name of the field, without actually loading it beforehand, and then load it with the correct name - or can I somehow load it without using the fieldname and then query it?

      => In the end, I just need to find a way to make that flexible so that the LOAD - and the entire app - can't fail just because one fieldname in one of the six files_to_be_loaded is different.

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Flexibilize a fieldname in a LOAD
          Friedrich Hofmann

          Hi,

           

          I have found a way, but that works only for one single table - that is the problem: We are loading six tables with an asterisk in the FROM part (only one LOAD statement). With several separate LOADs, of course it would be feasible, but we just abandoned that approach.

           

          For just one table, I can do the following:

          - FIRST 1 LOAD from that Excel file without headers ("no labels")

          - Create a variable with PEEK that has the name of one field (so, one variable for each field)

          - LOAD once more ("embedded labels" from the Excel file, using the variables as fieldnames.

           

          <=> That won't work with our "asterisk_LOAD"  - or will it?

          - That kind of LOAD concatenates all the tables, doesn't it? Well, that's where it breaks as the fields are supposed to be the same.

          <=> Can't we replace that "asterisk_LOAD" with some kind of LOOP that will just load all of the files that are there?

           

          P.S.: One possibility would be to use FILENAME to query the name of the file and execute the "asterisk-LOAD" for all the other tables where the fieldname is always the same and then do one more LOAD for this particular file.

          <=> That is still two LOAD commands. Can't we do it in one (or in a LOOP)?

            • Re: Flexibilize a fieldname in a LOAD
              Stefan Wühl

              What about adressing your columns using @1, @2, @3 .. (for xls files) or A, B, C... (for xlsx files) and aliases?

               

              LOAD @1 as Name,

                        @2 as Store,

                        @3 as Value

              FROM

              [Example1.xls]

              (biff, no labels, table is Example$, filters(

              Remove(Row, Pos(Top, 1))

              ));

                • Re: Flexibilize a fieldname in a LOAD
                  Friedrich Hofmann


                  Hi swuehl,

                   

                  that's just perfect!

                  I had the same solution, slightly more complicated - I was loading the top_row only, generating one variable each for the fieldnames and then loading the entire table, using the variables as fieldnames - but I missed that REMOVE option, so the second load would return me the fieldname as a value...

                  Your option, however, means I can do it in just one load.

                  Thanks a lot!

                  Best regards,

                   

                  DataNibbler