topic Variable for Where Clause and QVD Storage in App Development

Variable for Where Clause and QVD Storage

Rehan — Thu, 25 Jun 2020 20:33:46 GMT

Currently using Qlik Sense April 2020 Enterprise. I am looking to use a variable to restrict the data and create multiple QVD files.I would like to run some sort of a While loop which will run until all the variable values are used and QVD's created for each variable value.

For example

$(Variable) =A,B,C ........

Data_Table:

Select *

From Table

Were Field =$(variable)

Store Data_Table into \\Serve1\$(variable)\Data_Table.qvd

Re: Variable for Where Clause and QVD Storage

Vegar — Thu, 25 Jun 2020 21:54:53 GMT

Set vVariable = 'A', 'B', 'C' ;

For each v in $(vVariable)

Data_Table:

Select *

From Table

Were Field ='$(v)'

Store Data_Table into \\Serve1\$(v)\Data_Table.qvd;

Drop table Data_Table;

Next v

Re: Variable for Where Clause and QVD Storage

Rehan — Mon, 06 Jul 2020 15:26:20 GMT

Thanx for the solution. I have one more requirement to add. Once this loadd is done . I am dropping the table but still would like to get the row count for each v. I tried the below statement but it didnt work. Any Ideas?

Data_Table:

Select *

From Table

Were Field ='$(v)'

Let vRowCount_Data_Table_$(v) =NoofRows('Data_Table_$(v)')

Store Data_Table into \\Serve1\$(v)\Data_Table.qvd;

Drop table Data_Table;

Next v

Re: Variable for Where Clause and QVD Storage

Vegar — Mon, 06 Jul 2020 18:09:17 GMT

You don't have tables named 'Data_Table_$(v)' your table name is 'Data_Table'.

Try this:

Set vVariable = 'A', 'B', 'C' ;

For each v in $(vVariable)

Data_Table:

Select *

From Table

Were Field ='$(v)'

Let vNoOfRows_$(v) = NoOfRows('Data_Table') ;

Store Data_Table into \\Serve1\$(v)\Data_Table.qvd;

Drop table Data_Table;

Next v

Re: Variable for Where Clause and QVD Storage

Rehan — Wed, 08 Jul 2020 01:45:47 GMT

Thanks for the Prompt responses . ONe last requirement. I would like to create a table for Row count for every single $(v) value. Something like below which I can create as a Table on Front end.

How I can do that .

Load

A, 200,900

B, 400,900

etc.

Re: Variable for Where Clause and QVD Storage

Vegar — Wed, 08 Jul 2020 07:08:39 GMT

Try this:

Set vVariable = 'A', 'B', 'C' ;

For each v in $(vVariable)

Data_Table:

Select *

From Table

Were Field ='$(v)'

Let vNoOfRows_$(v) = NoOfRows('Data_Table') ;

Rowcounts:

Load

'$(v)' as Variable,

NoOfRows('Data_Table') As NoOfRows

Autogenerate 1;

Store Data_Table into \\Serve1\$(v)\Data_Table.qvd;

Drop table Data_Table;

Next v