topic Re: How to automate the final table box selection? in QlikView

How to automate the final table box selection?

Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I want to fully automate the process starting from execution of the script to generation of the report. I am successful in implementing the first part through a scheduler. The problem lies in the fact that how am I going to automate the selection process of the columns in the table box format? Presently I manually select the fields and add them on the right hand side under 'Fields Displayed in Tablebox' and click 'Apply' to generate the report. I don't want this process to be manually done.

Can someone please help me out?

Thanks and Regards,

Arghya

Re: How to automate the final table box selection?

vinieme12 — Mon, 24 Apr 2017 14:29:54 GMT

Trigger the selections and Printing of the report on the event "On Open Document"

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/QMC/Content/QMC_Documents_SourceDocuments_Triggers.htm

http://www.qgate.co.uk/knowledge/adding-default-selections-in-qlikview/

Re: How to automate the final table box selection?

Anonymous — Tue, 09 May 2017 22:06:28 GMT

Does the number of columns change every time? Do you just need the data exported to Excel or something?

Can you explain a little more?

Re: How to automate the final table box selection?

Wed, 10 May 2017 11:59:01 GMT

Hi Wallow,

No the number of columns don't change every time. I am providing an example screenshot of my requirement here

These two fields say 'A' and 'B' are fixed. Each time I run the script, these two fields will appear in the available fields for selection and I need both the fields to generate the report. Now the requirement is that I don't want to click 'Add All>>' and then select 'Apply' tab to generate the table box report. This should be automatically done after the execution of the script so that no manual intervention is required. After this another requirement is that the report generated should be exported to excel.But first of all this selection process has to be automated.

Thanks and Regards,

Arghya

Re: How to automate the final table box selection?

RonaldDoes — Wed, 10 May 2017 12:16:43 GMT

Hi Arghya,

If you place the table box with the correct fields in the QVW, then after reload you would just have to pick up the table box object, right?

Why would you want to add these fields every time the report is generated?

Re: How to automate the final table box selection?

Wed, 10 May 2017 13:18:44 GMT

Hi Ronald,

Actually the screenshot I have posted is mistakenly of the list box selection step. I don't require that. The correct screenshot is

Now I want to automate this step and what I want is this:

the table box directly after the execution of the script. I need to know where do I have to make the changes to bypass the previous step so that I don't have to select the fields manually.

Any help will be highly appreciated.

Thanks and Regards,

Arghya

Re: How to automate the final table box selection?

vinieme12 — Wed, 10 May 2017 13:24:53 GMT

why do you need to create this table box everytime you do a reload??

Re: How to automate the final table box selection?

avinashelite — Wed, 10 May 2017 13:38:22 GMT

In Table box we don't have the capability to handle the fields display via expression , so one way to make this happen is to create the place holder fields in the script and add them in advance

You could check for any macro code to do this dynamically

use the straight table in place of Tables box and based on the availability of the field hide and show the field with the help of the expression

Hope this helps you to get some idea

Re: How to automate the final table box selection?

Wed, 10 May 2017 13:42:23 GMT

Hi Vineeth,

I need this to export the table box to an excel sheet by selecting the 'XL' option present on the top right corner of the table box. I need to automate this step also but unable to achieve it till now. As I am new to Qlikview, I am asking these very basic questions may be but I will be grateful for your help. As I am manually exporting the table box to an excel sheet, I need to create this table box every time I do a reload.

Thanks and Regards,

Arghya

Re: How to automate the final table box selection?

avinashelite — Wed, 10 May 2017 13:53:01 GMT

Why don't you had a table box as an object in your QVW and keep it ? so that every time its refresh its always their no need to add every time . Its very basic i don't know what stopping you here ?

Re: How to automate the final table box selection?

Wed, 10 May 2017 13:56:19 GMT

Hi Avinash,

What is 'place holder fields' that you have asked to create in the script? How to add them in the script? Could you please explain me in a bit detail with some screenshot or examples.

Besides what is a 'straight table' and what is a the process you have asked to follow in the third alternative statement?

Thanks in advance!!!

Regards,

Arghya

Re: How to automate the final table box selection?

Wed, 10 May 2017 14:00:43 GMT

Hi Avinash,

The problem is that there is a huge amount of data and it changes almost in every two days. I generate the report in every two weeks. So when the I keep the table box in my QVW and reload the script, instead of getting the data refreshed, the execution gets hanged due to refresh of such huge data.

Thanks and Regards,

Arghya

Re: How to automate the final table box selection?

avinashelite — Wed, 10 May 2017 14:03:55 GMT

Is both the fields are coming from same table ? then data size should not be an issue

Re: How to automate the final table box selection?

avinashelite — Wed, 10 May 2017 14:05:25 GMT

If your end intention is to get those 2 columns into excel then use a direct store statement in the script so that you no need to come to front end for the data extraction

Re: How to automate the final table box selection?

Anonymous — Wed, 10 May 2017 14:27:38 GMT

Can you use the Store command and just export it as CSV?
Would that meet your need?

Re: How to automate the final table box selection?

Thu, 11 May 2017 12:09:29 GMT

Hi Wallow and Avinash,

The store command helped me to export one table to csv. But in the script I have multiple tables under which I have two or three columns. Some of the columns are required for the report generation and others are not required. So in that case how will it be possible to use the store command.

eg say

Table1:

Load a,b

from xyz.csv;

Table2:

Load c,d

from pqr.csv;

Now I want a and c as my final cols in the output. How am I gonna use the store command.

Thanks and Regards,

Arghya

Re: How to automate the final table box selection?

Anonymous — Thu, 11 May 2017 15:01:11 GMT

You would need to create another table in the script that combines the fields into the same table using a join command. Then run your Store command. You could then delete the extra table in the script.

================================

As an option to your original scenario of using a table in the UI...

Addressing your earlier comment,

"...The problem is that there is a huge amount of data and it changes almost in every two days. I generate the report in every two weeks. So when the I keep the table box in my QVW and reload the script, instead of getting the data refreshed, the execution gets hanged due to refresh of such huge data. "

You could try entering a calculation condition on the table object so it doesn't calculate until post reload. Maybe something like today()=makedate(year(ReloadTime()),month(reloadtime()),day(ReloadTime()))

If you need to automate exporting the table from the UI to excel you will need to use either a macro (see link below) or add-on like Nprinting.

Export To excel