Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Extracting values from a table and storing in another

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

2 Replies
MVP
MVP

Re: Extracting values from a table and storing in another

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

Not applicable

Re: Extracting values from a table and storing in another

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

Community Browser