topic Re: Store values of variable in qvd in QlikView

Store values of variable in qvd

flo2 — Wed, 18 May 2022 12:47:14 GMT

Hello all,

I would like to keep track of some variables that I calculate thanks to set analysis.

( My idea was to store the values of the different variables in a QVD with a column for each variable and everytime i reload it adds the new values of the variables inside the qvd as a new line. Also when I reload it import the previous values of those variables to make an historical data graph )

So there are a lot of topics on these subjects ( like this one https://community.qlik.com/t5/New-to-Qlik-Sense/Incremental-Load-on-QVD/td-p/1309066 )

But It appears that i don't fully understand.

Because what I did was:

1) Declare the variables ( I was not sure if I had to put an equal sign or not inside

Let v_indic_1 ='count({(1<[field ]={"fvalue"}>+.... ])'; Let v_indic_2 ='=count({(1<[field]={"value "}>+1<[field]=] .... )';

2) Load previous values of variables inside the qvd and concatenate with the new ones then store all in the qvd.

ALOS: LOAD v_indic_1, v_indic_2, v_indic_3, v_indic_4, v_indic_5 FROM [...\ ALOS.qvd] (qvd); concatenate Load '$(v_indic_1)' as v_indic_1, '$(v_indic_2)' as v_indic_2, '$(v_indic_3)' as v_indic_3, '$(v_indic_4)' as v_indic_4, '$(v_indic_5)' as v_indic_5 AutoGenerate 1; Store ALOS into [ALOS.qvd] (qvd);

That works but it stores the text of the formula inside the qvd and not the values of the variables.

I tried also without the ' ' to load the new values of variables ( '^(v_indic_5)' as v_indic_5...

If someone has an idea that would be with pleasure to listen 🙂

thanks for reading !

Re: Store values of variable in qvd

ZouhairLahlou — Wed, 18 May 2022 12:55:10 GMT

Hi Flo,

Try like this :

ALOS: LOAD v_indic_1, v_indic_2, v_indic_3, v_indic_4, v_indic_5 FROM [...\ ALOS.qvd] (qvd); concatenate (ALOS) Load $(v_indic_1) as v_indic_1, $(v_indic_2) as v_indic_2, $(v_indic_3) as v_indic_3, $(v_indic_4) as v_indic_4, $(v_indic_5) as v_indic_5 AutoGenerate 1; Store ALOS into [ALOS.qvd] (qvd);

Re: Store values of variable in qvd

flo2 — Wed, 18 May 2022 13:33:47 GMT

Thanks for the response but in fact when i remove the simples quotes, there is a error message.

" Syntax error Unexpected token: '{', expected one of: '(', ',', 'DISTINCT', 'Yellow', 'ZTestw_z', 'OPERATOR_PLUS', 'OPERATOR_MINUS', ... concatenate (ALOS) Load count(>>>>>>{<<<<<<(1<[Field]={"value"}>+1<[field]={"value"}>+...]) as v_indic_1,

But, indeed when i put simples quotes, it is just text 😕

Re: Store values of variable in qvd

eddie_wagt — Thu, 19 May 2022 12:32:39 GMT

These are front end variables/expressions (set analysis) which will not evaluate in the load script. What is exactly the reason you want the values of the variables in a QVD?

Regards Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.

Re: Store values of variable in qvd

flo2 — Tue, 24 May 2022 13:34:10 GMT

In fact I show the values of these set analysis directly on my application.

But i would like to keep records of former and current values to show them in a graph.

So i thought about storing the set analysis values in a QVD and add the current values eveyrtime i reload.

But my solution seems not good so 🤔

Re: Store values of variable in qvd

eddie_wagt — Tue, 24 May 2022 14:12:42 GMT

Hello @flo2 ,

It is better to calculate these measures in the loadscript and than you can store it in a QVD, but don't use set analysis in the loadscript.

Regards Eddie

Re: Store values of variable in qvd

flo2 — Tue, 24 May 2022 14:18:06 GMT

Ah thanks ! So I just need to convert the set analysis in expression in loadscript ?

But sometimes it's impossible to convert complex set analysis to simple expression no ?

( in my case it is not problematic because my set analysis are not complex )

Re: Store values of variable in qvd

eddie_wagt — Tue, 24 May 2022 15:04:07 GMT

Yes, you have to convert it in the loadscript. It can be complex indeed to calculate but not impossible. If you need any help with it, let the community know.

Re: Store values of variable in qvd

flo2 — Tue, 31 May 2022 14:20:57 GMT

@eddie_wagt wrote:

Yes, you have to convert it in the loadscript. It can be complex indeed to calculate but not impossible. If you need any help with it, let the community know.

Hey back and thanks again. I think it is better to continue on this subject as it is the same question.

( I would like to store in a qvd file an historic of values of some variables and also store in this qvd file the day of when we stored them in this qvd )

As it was needed, I converted the variables that i had before as set analysis in the loadscript. ( they work )

To make it work, i have this script :

var_prom is an example of variable i would like to store in qvd file.

And the lines in commentary currently are the lines i would like to use to recover the former values of the variable I stored in the qvd. Then concatenate these values with the current values.

let var_prom = count(if(match([Intern step],'...',1,0) and match([formular],'...',1,0) ,1)); /* ALOS: LOAD promEmbaucheChiff1 FROM [ALOS.qvd] (qvd); concatenate (ALOS) */ ALOS : Load $(var_prom) as promEmbaucheChiff1 AutoGenerate 1; Store ALOS into [ALOS.qvd] (qvd);

But i have an error, i think it doesn't recognise my expression for my variable . I have a Syntax error when i try to use the variable with $(var_prom)

Syntax error
ALOS :
Load
as >>>>>>promEmbaucheChiff1<<<<<<
AutoGenerate 1

Would you have some ideas to make it work ?

thanks

Re: Store values of variable in qvd

marcus_sommer — Tue, 31 May 2022 15:22:22 GMT

If the variable contains a (correct formatted) number you could call them with $(var). If the content is instead a string you need to add single-quotes to the variable like: '$(var)'.

In your case the variable will be NULL because you used LET and this means the specified content will be evaluated before the result is assigned to the variable - and you couldn't apply a count() within a variable because it would need a load-statement.

In general you could not grab any UI results within the load because to the time the load runs no UI exists.

A workaround to your aim might be to use two variables for one calculation - one contains the expression and the another will be per action/macro filled with this result - triggered by a button or any other method. And the value of this second variable is then available by the next load-run.

By just a few occurrences it's a pragmatic approach but by many of them and/or creating a rather general approach for multiple applications you should be investigate other methods - for example a write-back logic into any data-base which your load could later read again.

- Marcus

Re: Store values of variable in qvd

flo2 — Wed, 01 Jun 2022 09:59:39 GMT

Thanks, it seems very complex, i was searching about examples of these kinds of solutions on community.qlik and it seems pretty difficult.

Would it be possible to, instead of keeping tracks of former and current values of variable, to keeping track of the former values of text objects ?

I mean, I would not not store the values after reloading of the values of text object but I would store the value before the reloading. To do this, I would store in a qvd file the current values shown in the app in these text objects ( not the values after the reload but the value before the reload )

I was thinking about using macro at the opening maybe ? ( i have to check if it will work even for app in access point ) to be able to export directly values of text objects to qvd files ( so the values before reloading cause if I understand well it will be very complex to have the values after loading )

Re: Store values of variable in qvd

marcus_sommer — Wed, 01 Jun 2022 11:54:06 GMT

Nearly each object could be exported. By a text-box I'm not sure if qvd as file-format would be possible whereby why not just taking a table-chart? It could be used for multiple variables and also contain further columns for a name, comments and other stuff.

Important is such macros/actions are only possible if the document is opened - within the desktop client or the IE plugin because within the AJAX client the most macros aren't possible respectively have other restrictions (by server-side macros).

What is the aim behind all this? Why should the next reload loading any UI stuff from the application?

- Marcus

Re: Store values of variable in qvd

flo2 — Wed, 01 Jun 2022 12:14:43 GMT

About the aim behind this it is that currently my Qlikview app calculates five KPI ( before it was KPI calculated thanks to set analysis on UI but now i converted them to script variables that i display on text object )

So there are these 5 KPI that I would like to keep a trace of, i mean keep the old values of them and do a graph about the evolution of them.

So I wanted that at each reload of the app the values ( or script variables or value of text objet where the kpi are displayed ) are writen in a qvd file.

Also at each reload it loads the data of the former values from the qvd.

And so, with the potentiel restrictions i think it won't be possible with macros but i'm going to search some possibilities

Re: Store values of variable in qvd

marcus_sommer — Wed, 01 Jun 2022 12:59:07 GMT

If I understand your description right then happens the KPI calculation further within the UI - only string-variables are used as expression instead of a manually written expression? Because if these KPI would be directly calculated within the script you could easily store them from there as qvd.

Have you ever considered to do these calculations within the script? If this should be really not possible I would repeat my suggestion from above to investigate if a write-back approach could be implemented.

- Marcus

Re: Store values of variable in qvd

flo2 — Wed, 01 Jun 2022 13:22:45 GMT

Ah ! I think i didn't understand well your previous response because of my poor english 😁

Because i thought that even if I did the calculations in the script i couldn't make write the values in a QVD file

I tried like that :

let var_prom = count(if(match([Intern step],'...',1,0) and match([formular],'...',1,0) ,1));

/*
ALOS:
LOAD 
promEmbaucheChiff1
FROM
[ALOS.qvd]
(qvd);


concatenate (ALOS)

*/ 
ALOS : 
Load
$(var_prom) as promEmbaucheChiff1
AutoGenerate 1;

Store ALOS into [ALOS.qvd] (qvd);

( the ALOS.QVD is the QVD where i would like to put the values of the variables.

I concatenate the data to have the old values and the current values )

But i have an error, i think it doesn't recognise my expression for my variable . I have a Syntax error when i try to use the variable with $(var_prom)

Syntax error
ALOS :
Load
as >>>>>>promEmbaucheChiff1<<<<<<
AutoGenerate 1

Because var_prom is a KPI i would like to put in a QVD file.

Re: Store values of variable in qvd

marcus_sommer — Wed, 01 Jun 2022 13:32:42 GMT

Like above already mentioned such a calculation couldn't be done within a variable in the script. You need to create a table (pulling all relevant fields together and applying the needed transformation and aggregations) which may end in just a single row + single column = single value - which may then be stored within a qvd or also assigned to a variable.

- Marcus

Re: Store values of variable in qvd

flo2 — Wed, 01 Jun 2022 14:09:17 GMT

It was indeed as I said my bad understanding of english, thanks a lot it works !