Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
richardouellett
Creator
Creator

Create Script Flag Field for Add-On Lab test Orders

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

Example.jpg

2 Replies
richardouellett
Creator
Creator
Author

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
Support
Support

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...