Qlikview Enterprise, DateTime subtraction

Find this post interesting? Do you like interesting things? Maybe you would like my invention, a connectible candle called a WickBrick!

Get one here http://wickbrick.etsy.com/


I am really loving the Qlikview forums, they are starting to fill up with more and more useful data! I was quickly trying to figure out how to do date subtraction, figuring there was some sort of duration function. A fairly quick search of http://www.qlikcommunity.com brought me this solution by tmy


So I used =interval(now() – myTimeStamp,’hh:mm:ss’)

And now you know!

You know what is awesome? using an IBM 3850 m2 server to…

Install ESX 3.5 and then download the Nostalgia Virtual Appliance, then play Blockout or Prince of Persia. Wow

10 Million strong, and growing

I must admit it, I’m an adult and I take flintstone vitamins. I can’t stand ‘regular’ vitamins, so I take the chewable ones instead. Oh, I also try to eat my veggies as well as fruits 🙂

Working on first Patent

Never having written/filed a patent before it has been an interesting exercise so far. Obviously, I can’t say much more than that I am working on an improved candle. Its interesting that most places recommend keeping an inventors notebook with bound pages, writing in pen ect… but you would think there would be a way to digitally time stamp something to prove that you were working on it.  

Television, a mind and energy sucker

Recently, I was feeling a little tired so I thought why not watch some TV to rest up a bit and then get on with some projects (I normally watch very little TV). Well, an hour later I realized that not only was I not relaxed or refreshed I was in fact without any feeling of initiative and apathetic towards my projects. Last night, instead of watching TV I actually did stuff.

How to setup Adage to allow QC_TestResults

How to setup Adage to allow QC_TestResults to be passed as an AsynchBarcode transaction.

I wanted to be able to use ATF files to submit QC_TestResults. Here are some of my findings.

In the Adage Explorer, under Applications, there is a barcoding folder. First thing you need to do is change the Lot QC Test Results to use the Transaction Event Engine. (It is possible this is not needed, since it appears to work even if it is not checked.)

To check mark this setting click barcode transaction, then choose Lot QC Test Results, click update, then check Use Transaction Event Engine, then click Update.

If you notice on this screen you can see the format of the Lot QC Test Results.

First is Item, Pack, Lot, – -Test, – -Effective Date, – -Seq Num, – -Operator, – -Test Value, – -Result, – -Status, – -Tested By, – -Tested Date, – -Notes, – -Disposition Code, – -Disposition Comments

These directly relate to the format of the ATF. For example DT_PROMPT1 is Item, DT_PROMPT2 is pack etc…















HD_USER=emorris <==User

HD_WHS=DSW <==Warehouse

DT_PROMPT_1=100100 <==Item

DT_PROMPT_2= <==Pack


DT_PROMPT_4=Ecoli <==Test name

DT_PROMPT_5=1/2/1998 <==Test effective date

DT_PROMPT_6=1 <==Test sequence number

DT_PROMPT_7== <== Value is > < or =

DT_PROMPT_8=2 <== Test value

DT_PROMPT_11=emorris <== Tested by

DT_PROMPT_12=03/11/2008 <== Tested date

DT_PROMPT_13=This is a test of notes <== Notes field

DT_PROMPT_14= <== Disposition code

DT_PROMPT_15=Disposition comments <==Disposition comments

DT_PROMPT_16= <== If you were performing more than one test, than this field would contain the next test name.



In this example from adage they had the test type as HD_TRANDEF_KEY=30

I was getting strange results from that and finally did a search for HD_TRANDEF_KEY what I found was a list of valid Trandefs were contained in bc_trandef_tbl. I found that Lot QC Test Results was actually HD_TRANDEF_KEY=31.

To get a successful test result submitted a few things are absolutely necessary and others are not.

The PRFX_DOC_NBR can be blank; it’s annoying in that it only allows 10 characters.

PRFX_DATE should be in month/day/year format and is required

PRFX_TIME is required.

PRFX_CONTROL_NBR is required and must be unique (stored of course in our favorite ei_queue_tbl)

HD_TRANDEF_KEY=31 (explained above, required)

HD_USER= (must be VALID user)

DT_PROMPT_1=item (required duh)

Items with lots appear to be in en_item_key

DT_PROMPT_2=pack (REQUIRED, if it is blank I believe it is a bulk order)

Packs in en_itqct_tbl

DT_PROMPT_3=lot (required)

Lots with tests attached in qc_specs_lotapproval_testsresult

DT_PROMPT_4=test name (Must Match Exactly)

Tests in QC_Items_specs

DT_PROMPT_5=Test Effective Date (THIS MUST MATCH, otherwise the file will make it into completed but will not update the results)  THIS MUST MATCH DATE AND TIME!

In QC_Items_specs the max(en_itqct_effdt)

DT_PROMPT_6=test sequence number (required, 1)

DT_PROMPT_7= this is the sign, should be < = or > (NOT needed for qualitative)

DT_PROMPT_8= test value (NOT needed for qualitative)

DT_PROMPT_9= this is a free form text field for the ‘result’ up to 256 char

DT_PROMPT_10= this is the status of the qualitative test (REQUIRED for Qualitative)



DT_PROMPT_11= tested by (haven’t tested whether required or whether needs to be valid user or not)

DT_PROMPT_12= Tested date and time, 4/3/2008 8:07 AM

ATFSUM_CHECK=* (this is valid, with a control line feed after it)

In my case, VeggieTests.exe generates this info as an .XML file











<TimeStamp>10:35 AM</TimeStamp>




















<Scanneddata4>Aero Rob</Scanneddata4>


























<Scanneddata12>4/3/2008 10:35 AM</Scanneddata12>








This is then converted by the TransformTool.jar

With the below script. What it does is take the definition of an AsyncBarcode from c:\AsyncBarcode_Inbound.xsl converts it into an .EIF and moves it to C:\einterface\Inbound\New

**************TransformTool setup file(InputFile.xml)********

<?xml version=”1.0″?>

<!DOCTYPE InputFile SYSTEM “C:\InputFile.dtd”>






















**************TransformTool setup file(InputFile.xml)********

To test, assuming you have a test set up already (under Application, Enterprise, Quality Management, QC Test)

Next you have to create a specification (QC Specification) that will match your item and pack. And then attach your Test to it.

Now you can attach your specification to your lot. (Lot Specification)

That lot should now go on hold.

Now you can submit your test result ATF files. If everything works right the lot will go off hold.

To view test results go to Lot Test Results and select your Item, Pack, and Lot.

Firefox 3 Beta 5

I am really enjoying the latest version of Firefox. Gmail is much snappier! The Internet just seems faster!