topic How to create a table and insert data from visualization? in Integration, Extension & APIs

How to create a table and insert data from visualization?

KohSamui — Sat, 26 Nov 2022 09:11:57 GMT

Is it possible to create a new table (tables) from visualization component and insert/initialize data from visualization code? If there is some example code somewhere it would be excellent help.

(QuickTableViewer component was a great example how to query tables and fields and let user to choose from them. Now I would need a way to create tables and initial data according to users selection).

Re: How to create a table and insert data from visualization?

alex_colombo — Mon, 28 Nov 2022 08:53:05 GMT

Hi @KohSamui , not totally clear what are you trying to achieve. Do you want to create a new table with dimensions and measure in it from an extension object?

Re: How to create a table and insert data from visualization?

KohSamui — Mon, 28 Nov 2022 14:49:32 GMT

Hi Alex,

In our case user imports initial data to Qlik normal way using data manager.

But our extension uses initial data to do some automatic analyzing by searching fields from tables and then user confirms that our extension has done correct guesses according to field names. After that our extension reads data from user confirmed fields and extension creates new tables for persisting some data that is calculated inside the extension (because initial data analyze and logic takes time and should be done only once initial data is imported).

So what I need is an example of API usage where extension creates new table with new fields and inserts rows into that table.

Kind Regards

Re: How to create a table and insert data from visualization?

alex_colombo — Mon, 28 Nov 2022 15:15:22 GMT

@KohSamui ok, so basically which APIs method to use for create a Qlik visualizations (a table in your example) directly in a Qlik Sense app with dimensions and measures, correct?

Re: How to create a table and insert data from visualization?

KohSamui — Mon, 28 Nov 2022 20:24:38 GMT

Almost but not quite. I try to express myself more clearly:

I need to create data table (into data model) with several columns and add some thousands of data rows - and I need to do that from javascript code in visualization extension. (my code creates the data - it is not imported by user using data manager and csv or db connection).

I know how to read data with qHyperCube but I do not know how to modify and store new data (and how to create empty data table into data model).

Re: How to create a table and insert data from visualization?

alex_colombo — Tue, 29 Nov 2022 10:54:05 GMT

@KohSamui that's clear. Basically you are allowing end users to modify reload script, insert new data and then reload data? It's a little bit scary from my point of view. What are you trying to achieve?

Anyway, for doing what you are asking you have to use Engine APIs:

Use setScript and doSave method for changing and saving the reload script where you can insert your new empty tables or new tables with rows (you have to read rows somewhere, for a DB from a CSV or you can hardcode data)
Use doReload method for reloading your app
Then your new data are in the datamodel and you can use them in your extension

Re: How to create a table and insert data from visualization?

KohSamui — Tue, 29 Nov 2022 14:24:02 GMT

Thank you Alex, this seems a bit complex to me. Sorry, I am new in BI tools and terms and concepts are new to me.

Is this the only way to modify data and datamodel from visualization extension?

Currently our workflow goes like this:

User imports a huge log file into system using normal Qlik data manager tools in UI (using csv or cloud source)
Some other admin person checks the imported log file and uses external ETL and python scripts to create bunch of new csv files and and then uploads those to Qlik and manually make associations etc. This is the phase that we would like to automate so that visualization extension does this from javascript code somehow. (reads available tables and fields, asks user to confirm which fields are used, creates new tables and columns and calculates initial data into new tables).
Visualization component asks user to select dimensions and measures, which is error prone. This phase we would like to be automatic also so that user doesn't have to manually select anything, because we can trust that our code in phase 2 has created predefined dimension names etc.

We are currently evaluating our options for implementing this somehow.

Re: How to create a table and insert data from visualization?

KohSamui — Fri, 02 Dec 2022 08:43:36 GMT

Is it so that hyper cube and qlik data model can not be used as a data base? (From visual extension javascript code)

Hyper cube and qlik data model are only for querying data from visualization? Not for writing and modifying?

setScript, doSave and doReload methods seem like a hack for this purpose.

Re: How to create a table and insert data from visualization?

alex_colombo — Fri, 02 Dec 2022 08:50:30 GMT

Qlik data model is the "database" of an app, where visualizations get data. Hypercube is an object where you can find data from data model, based on dimension and measures declared in the hypercube itself.

For writing and modifying "database" of an app you have to change or reload the reload script of an app. You can do it manually from Data Load Editor or via API, as mentioned above with setScript, doSave and deReload.

Re: How to create a table and insert data from visualization?

KohSamui — Thu, 08 Dec 2022 18:05:48 GMT

Thank you.

Is there some example code of this kind of database modification?

Re: How to create a table and insert data from visualization?

alex_colombo — Mon, 12 Dec 2022 10:34:34 GMT

@KohSamui which kind of documentation are you looking for? About the APIs or about how to insert data in an app with the reload script?