5 Replies Latest reply: Sep 4, 2012 3:46 AM by whiteline _ RSS

    For each

    Giorgia Michieli

      Hello,

      in a table I have cf_<num> fields that are not in series and are, for example, cf_148, cf_278, and cf_456.

      Since these fields change from time to time, I would like a standard way to get them on without having to 'select *'.

      I would like something like

       

       

      FOR I =1 TO 999

                LOAD

                               code,

                               cf_$(I) as cf_$(I)

                FROM

                [Table.QVD]

                (qvd);

       

      NEXT I

       

       

      But I can not do this because in the table I don't have cf_1, cf_2, cf_3 etc. but (for example) only cf_148, cf_278 and cf_456.

      Is there a way to do this with something like 'where exists cf_$(I)' ?

        • Re: For each
          ioannis giakoumakis

          ExistingCfs:

          LOAD * INLINE [

              ExistingCfs

              cf_2

              cf_3

              cf_7

          ];

           

           

          test:

          LOAD 0 as cf

          AutoGenerate(0);

           

           

          for a=1 to 9

          Concatenate (test)

          load 'cf_$(a)' as cf

          AutoGenerate(1);

          next;

           

           

          Inner Join (test)

          LOAD

                    ExistingCfs as cf

          Resident ExistingCfs;

           

           

          DROP Table ExistingCfs;

            • Re: For each
              Giorgia Michieli

              Interesting, but not exactly what I want.

              Let me explain: I have a table with several fields, among them are the type cf_ <num> cf_123, cf_370, cf_264 which vary from time to time.

              This means that in the same table today I find cf_123, cf_370, cf_264, tomorrow it could be added cf_692.

              I do not want to have to dip into the QlikView script every time you add a new field, so I want something standard without having to manually enter the new field as in the case load inline.

              Now I have

               

              Table:

              load

                   code as codice,

                   name as cognome,

                   surname as nome,

                   date(time_acc) as data,

                   applymap('Lingua', account) as azienda,

                   cf_123,

                   cf_370,

                   cf_264

              from [Table.qvd]

               

              you see, I have to manipulate some fileds except cf fields that are time-varying.

              I thought you could do something like this:

               

              Table:

              load

                   code as codice,

                   name as cognome,

                   surname as nome,

                   date(time_acc) as data,

                   applymap('Lingua', account) as azienda

              from [Table.qvd]

               

              left join

              load

              code as codice,

              ####  --> I don't know how I can insert here cf fields whithout writing them explicitly

              from [Table.qvd]

               

               

              What Ioannis Giakoumakis wrote can work if you write the fields explicitly, even if what I want is not to have a field that contains cf_fields, but bring out cf_fields as they are.

               

              What I want to get is the starting table, but without having to explicitly write the cf_fields:

               

              Table:

              load

                   code as codice,

                   name as cognome,

                   surname as nome,

                   date(time_acc) as data,

                   applymap('Lingua', account) as azienda,

                   cf_*

              from [Table.qvd]

               

               

              Have I explained better?

            • Re: For each
              whiteline _

              As first step use Transform feature to rotate the table and load desired field names to a separate table as a field.

              Then concatenate them into one string field separated by commas with concat() and group by.

              Then you can assign the resulting value to a variable with fieldvalue().

              Then you can use that variable to parametrize your load (as Ioannis suggested).