Create Script Flag Field for Add-On Lab test Orders

[richardouellett]

Hi

I’m seeking help trying to create a Flag Field to catch add-on laboratory tests and have provided attached Excel workbook and QVW consisting of ER orders for a single day. 

By definition each ER patient is assigned a unique Accession number and 1 or more lab tests can be ordered. An add-on test is defined as any unique Accession with 2 or more tests where 1 or more of the tests has a Order Time Stamp 10 minutes greater than the “minimum” Order Time  Stamp.  By way of example in the image below the “Minimum” timestamp for this accession is 9:30 AM and the “DiffAuto” test was ordered 19 minutes after the original order so a the new “TestAddOn” flag field would be “True” or = 1.

The data set I am using is large so, optimizing the creation of this Test Add-on Flag withing the script is important.  Suggestions would be appreciated.

Thanks

Rick

[richardouellett]

From a solution Sunny Talwar provided in another thread the following script seems to work...  Are there other approaches?

TestDetail:

LOAD Accession, OrdStamp, TstName

FROM

(biff, embedded labels, table is Sheet1$);

Left Join (TestDetail)

LOAD Accession,

    Min(OrdStamp) as MinOrdStamp

Resident TestDetail

Group By Accession;

FinalTestDetail:

Load *,

If(OrdStamp=MinOrdStamp,0,1) as AddOn

Resident TestDetail;

Drop Table TestDetail;

[Dalton_Ruer]

You have nailed the concept ... you can TRANSFORM data on the fly like that to create additional fields. 

The 0 and 1 is meaningful to us data dorks. But end users would rather see "Yes" or "No", "True or False" you get the idea. I want the end user friendliness. But I also want the speed of calculations using the 0's and the 1's instead of having create IF expressions. That's why I'm all about using the DUAL function. 

Check out these resources for information about how to use DUAL so you can also do "both."

Generic help on DUAL function 

Improving Application Performance (DUAL is included) https://event.on24.com/eventRegistration/EventLobbyServlet?target=reg20.jsp&referrer=&eventid=181710...

https://community.qlik.com/t5/Qlik-Healthcare-User-Group/You-are-invited-to-a-DUAL/gpm-p/1486276#M65...