Optimized load vs. formatting with duals

[krumbein]

I am facing the following issue.

We want our flag fields to be duals to make sure the user sees something useful, but the value is still recognized as a boolean in the loadscript.

Big fact tables might need concatenation in some datamodels. I am aware of the technique to concatenate the content of the QVD to an existing table using dummy fields and ensuring that every field of the existing table is loaded again to keep it optimized.

But when I combine those things and do something like 

Concatenate(Table1)
load
   SomeCommonField,
   Dummy   AS FlagFieldThatOnlyExistsInTable1
from
   Table2.qvd (qvd)
;

the formatting of the flag field is lost. If I make the Concatenate intentionally unoptimized, it stays intact, but that is obviously not what I want.

The attached QVW demonstrates the issue.

Is there any solution to this conundrum?

[sunny_talwar]

Why not keep the load optimized and use Document Properties to change the format for TestDatum1 to Date across the board for your whole application?

This settings should stay with every reload so you don't have worry about changing this after every reload.

[krumbein]

That would certainly accomplish the primary goal of keeping the format. For that field. In that application.

But my actual problem is the dual flag field TestValue and I don't see a way to change the formating for that in the UI.

Besides that not being an ideal solution, of course

[krumbein]

Anyone?

Maybe that is resolved in later versions, but I can't try any right now

[martinpohl]

you want to concatenate datas to table 1 (and as I understand, there the field is dual) from qvd-file.

In qvd file I think the field is not dual, so you have to use the dual statement in script again

Regards

[Levi1sJ1onas]

To make the mapping work, you would have to force an un-optimized load mcdvoice.

[krumbein]

It is Dual in the QVD. When I simply load it (no Concatenation with a DummyField), it  works as expected

[krumbein]

That is true, a mapping would not work in an optimized load. But I don't want to remap the values, I simply want to keep the formating, that already is in the field. And I would obviously like to try to avoid the un-optimized load

But I guess it is the special treatment of the optimized load, which somehow resets the text representation of the values in the field

[marcus_sommer · Accepted solution]

I think the essential point is to concat a none dual-value with a dual-value which creates a kind of mixed-value - not entirely synchronous values - which couldn't be handled without any processing. A resolving way might be to change this part:

...
AuxTable: load /*null()*/ dual('#NV', null()) AS Dummy AutoGenerate 1;
...

- Marcus

[krumbein]

Wow, that really did the trick!!!! 🙂

And the best thing about it is, that the #NV doesn't show up anywhere. It is still a proper null value, which doesn't show up in filter fields and can not be selected. Kind of makes sense as well

Thank you!

P.S. In late versions of QlikView and QlikSense this seems to be have been resolved even in my original version