2 Replies Latest reply: Apr 29, 2013 7:47 PM by Ravi Hulasi RSS

    Extracting values from a table and storing in another

      Hi,

       

      I'm having some challenges understanding an efficient way of iterating through a table on load and pulling out the required values into another table.  I'd like to iterate through the data and for every set of values that share the same value for Fldrid, pull out the earliest value of Notecreatedttm based on the value of Category Overall, with a preference of extracting 3 - High, then 2 - Medium, and 1 - Low.  For example, with the attached dataset, I'd like to generate a table that looks like this:

       

      FldridNotecreatedttmCategory Overall
      1001706/28/2002  9:153 - High
      1005277/11/2002 8:171 - Low
      1010817/13/2002 10:483 - High
      1011187/12/2002 15:531 - Low
      1029288/7/2002 8:092 - Medium

       

      I'm struggling with the basic premise of reading the source table and storing the value in another table.  When I do so with the attached qvw, the field definitions are incorrect.  Maybe this has something to do with my syntax when storing values in a second table?

       

      I'd really appreciate it if someone could at least point me in the right direction for simply reading values in one table and storing it in another. I can then have a go at putting some conditional logic to extract the values for Highs and Medium as I iterate through each row.  Of course, if there is a more efficient way to do this, perhaps with set analysis I'd be happy to hear it.  Would using a resident table make more sense?

       

      Thanks in advance,

      Rav

        • Re: Extracting values from a table and storing in another
          Stefan Wühl

          You don't need to use a for next loop in your script, you can do a group by load:

           

          SET TimestampFormat='M/D/YYYY h:mm';

           

          High_Exposure:

          LOAD Fldrid,

               Notecreatedttm,

               [Category Overall]

          FROM

          [sample he output_qv.txt]

          (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

           

          Result:

          LOAD Fldrid,

                    FirstValue(Notecreatedttm) as FirstValue

          Resident High_Exposure

          group by Fldrid order by [Category Overall] desc, Notecreatedttm;

           

          And take care to parse in your timestamp correctly (note the adapted TimestampFormat at the top).

           

          Hope this helps,

          Stefan