3 Replies Latest reply: May 5, 2014 7:04 AM by Lennart Frimannslund RSS

    Which functions can increase the number of rows?

      Hi,

       

      in a load script, using the function subfield can increase the number of rows. For example:

       

      LOAD subfield('A,B,C,D',',') as Letter

      Autogenerate 1;

       

      will produce four rows, one for each letter in the list. In other words, we get a four-row output table from a one-row input table. My question is: Are there any other functions which can increase the number of rows, in a similar way?

        • Re: Which functions can increase the number of rows?
          Tresesco B

          I guess there is no other such function. Having said that, if you can describe your real scenario, may be, you could be helped. There are options of using loop in the load. Most likely, you can look into While clause, that lets you read the same record repeatedly until a certain condition is met.

          Ex:

           

          My Tab:

           

          Load Student,

           

          mid(Grades,IterNo( ),1) as Grade,

           

          pick(IterNo( ), 'Math', 'English', 'Science', 'History') as
          Subject from Tab1.csv

           

          while mid(Grades,IterNo( ),1)<>' ';

            • Re: Which functions can increase the number of rows?

              Hi guys, and thanks for the quick replies!

               

              I thought I'd spare people having to read about my actual scenario, as it is quite complex but as you may suspect I am trying to avoid loops.

               

              Loosely speaking I have an application which evaluates each fact table input row, and sometimes (but but far from always) has to create more than one output row for each input row. A for-loop would likely be much too slow for my purpose. I haven't checked while loops, but it's worth a try.

               

              Anyway, subfield being the only function of its kind is useful information, so many thanks!

            • Re: Which functions can increase the number of rows?
              Anand Chouhan

              It depends on the scenario of using the table by any for loop or while loop the data increases but depends on the table structure by some joins in the table also the data increases.