Loading data is time consuming when used text()

[SRT10]

Loading data is time consuming when used text(). This is because of the text() method used while reading data.

Can this be optimised or done it better way.

My Code :

Data:

Load col1,col2,col3,text(col4) as col4

from D:/abc.qvd (qvd);

Is there any other solution. Have couple of such qvds from where we read data.

Qlik DataMarket  QlikView 

[tresesco]

Create the new column in the qvd itself, i.e. - when you create the qvd in that load statement itself. Then you could be able to load the qvd without transformation and it becomes optimized load which would much faster.

[SRT10]

Column is already present in souce qvd. but considering data issue we are using it in text method. Above code has * and so it looked like we are creating new column. Code seems to be below one :

Data:

Load col1,col2,text(col3) as col3

from d:/abc.qvd (qvd);

[tresesco]

Why not use text(...) in the earlier load when you create qvd?

[marcus_sommer]

To keep the load optimized you may apply a Touchless Formatting | Qlikview Cookbook. But I suggest to follow the suggestion from @tresesco because in most cases it's more suitable to apply a transformation as near as possible to the source and in your case the text() may come here too late because the qvd-data may have already lost their exact information.

- Marcus 

[SRT10]

I did went throught he touchless link but it has mentioned that it is not applicable for text().

if we use text() while generating source qvd then
1. there will be lot of work as there are 11 different qvd's
2. These qvd's are also used by other qvw so they will have impact if we do text() during their generation.

Our column will have data like -

000013123

C013121

123

1-A456

[marcus_sommer]

Within a very narrow interpretation it's true that there happens not a text() implementation. But within many scenarios you could apply the touchless-formatting approach also for an interpretation. Important is that the loaded values needs already to be strings within the qvd. If they are numeric you will loose the information already within the qvd.

To see what is meant and also to play a bit with the functionalities you may try the following within a test-application:

//t: load F as F1, text(F)as F2 inline [
//F
//000013123
//C013121
//123
//0123
//1-A456
//13123
//013123
//0013123
//];
//store t into t.qvd (qvd);

t0: load '0' as F1, '0' as F2 autogenerate 1;
t1: noconcatenate load * from t.qvd (qvd);
store t1 into t1.qvd (qvd);
drop tables t0;

exit script;

and commenting in/out the relevant script-parts. For me it results in:

To conclude - I suggest to transfer the text() already to the qvd-creation layer and to apply the logic to 11 cases seems not to be to cause too much efforts. If other processes also load from these qvd's you could double those fields and then each process takes those fields which it needs. Of course the qvd's will be then a bit larger but in regard to the effect it's probably not a high price to pay.

- Marcus