Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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