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
Hi Rob,
Thanks for the post.
Can u pls explain the ‘Survive Reload’ & how is it used here
Regards
When the “Survive Reload” property is checked, the assigned number format will not be changed by a reload — unless the script explicitly sets a format.
Unchecking “Survive Reload” is necessary to allow the “implicit” format assignment in the technique presented above.
-Rob
Thanks
By imlicit format do u mean the dual format?
shudn’t we call it the explicit format as it is assigned in the script ?
Rob,
I was thinking about this by using an include file. With the include file, one can keep multiple qvds optimized if they all use the shipped field.
Although I have not tried it yet, I will give it a try. Thanks for sharing this useful technique.
This is cool!