A clever customer reminded me of a useful script technique this week. Here’s the scenario.

A dashboard script was loading all fields from a qvd as:
LOAD * FROM data.qvd (qvd);

One of the fields, “Shipped”, was a flag field that contains the values 0/1. It became apparent in the dashboard UI that this would look better as a Dual() field with display values of Yes/No. It was agreed that the QVD should be modified to create a Dual(), but it will be some time before that change can be made. In the meantime, the change should be made in this dashboard script.

The Shipped field could be made a Dual() by modifying the QVD load statement in one of two ways.

1. Instead of “LOAD *”, list each field and apply the Dual() function to the Shipped field.
2. Add an additional field “Shipped2” and rename/drop to replace the Shipped field.

Both of these techniques share the downside of making the QVD load un-optimized, losing the speed advantage of optimized load.

Another possibility would be to create a Map with Dual() types and MAP the Shipped field:
MAP Shipped USING MyMap;
However, global mapping does not occur for optimized loads. To make the mapping work, you would have to force an un-optimized load.

Is there a way to change Shipped to Dual() while retaining the optimized load? Yes — here’s one way;

temp:
LOAD Dual(str,num) as Shipped INLINE [
str, num
Yes, 1
No, 0
];

data:
LOAD * FROM data.qvd (qvd)
;

DROP TABLE temp;

Before loading the QVD  we load a temp table with the field “Shipped” using the correct dual() values. The temp table is later dropped after the QVD is loaded. The key to making this effective is that you must uncheck “Survive Reload” for the Shipped field in Document Properties -> Number.

It’s an interesting technique and the extra blocks of code can be removed when the Dual() is eventually added to the QVD.

-Rob