Linking Qlikview and SQL table

Tue, 31 Aug 2010 14:37:56 GMT

I have a Purchase Order number that is loaded into a Qlikview Table from a text file. Every Purchase Order have an action;

Pull
Push
Cancel
Reconfirm

Each buyer writes a comment per purchase order that is saved in a SQL database. The Purchase Order number is the key. The buyer also selects a Status showing if the purchase order action is open, escalated or resolved.

I want to have a drop-down select or equivalent for the Status field, i.e. being able to select or resolved, escalated or open Purchase Orders.

I have written this Load Script (timestamp is the datetime when the comment was written);

LOAD po_key,
FirstSortedValue(Status,-timestamp) as POstatus
resident sqlPO
group by po_key;

This work perfectly as long as a Purchase Order has a comment in the SQL table. But if the SQL table doesn't have a Status for that Purchase Order then it doesn't work.

How do I write the script to make it work?

The comment is updated using Partial Reload and I'd rather not update the entire Qlikview Table which I assume I have to if I join the two tables.

---

Resulting Table

Purchase_order	Status	…
PO1	Resolved
PO2	Open
PO3	Open
PO4	Escalated
PO5	Open

SQL Comment Table

Purchase_order	Status	Comment
PO1	Resolved	test
PO4	Escalated	test

Qlikview Table (text file)

Purchase_order	Action	…
PO1	Pull
PO2	Cancel
PO3	Pull
PO4	Push
PO5	Pull

Linking Qlikview and SQL table

Wed, 01 Sep 2010 04:38:11 GMT

Presumably you need to provide a "default" status if there is no status in the sqlPO table.

What about FirstSortedValue(if( isnull(status), 'no status', status ),-timestamp)

topic Linking Qlikview and SQL table in QlikView

Linking Qlikview and SQL table

Linking Qlikview and SQL table