Extracting values from a table and storing in another

[#1]

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:

Fldrid	Notecreatedttm	Category Overall
100170	6/28/2002  9:15	3 - High
100527	7/11/2002 8:17	1 - Low
101081	7/13/2002 10:48	3 - High
101118	7/12/2002 15:53	1 - Low
102928	8/7/2002 8:09	2 - 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

[swuehl · Accepted solution]

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

[#3]

Thanks Stefan, that did the trick!  I also added in a definition to retrieve the category:

Result:

LOAD Fldrid,

     FirstValue(Notecreatedttm) as FirstValue,

     FirstValue([Category Overall]) as FirstCategory

Resident High_Exposure

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

Regards,

Rav