topic Re: New IDEA: Create master items (dimensions, measures) in script in App Development

New IDEA: Create master items (dimensions, measures) in script

hugmarcel — Tue, 18 Aug 2015 09:45:31 GMT

We are currently using QlikView, and here lots of KPIs (formulas) and dimensions (table fields).

The KPIs and dimensions are stored in an Excel file as meta data.

What we are definitely missing is the possibility to create master items within QSense script!

The current possibilities need manual work or using the API. The first option is not feasible having lots of KPIs and dimensions. And if changes apply, manual work must be re-done over and over.

The second one needs setting up a development area to handle JSON, API Calls etc. We do not want this. As e.g. for JSON, an additional transformation layer to get the correct input will be needed.

Check also these resources regarding the issue:
- https://community.qlik.com/docs/DOC-8864#comment-36907
- https://community.qlik.com/message/738830#738830
- https://community.qlik.com/message/607828#607828
- https://community.qlik.com/message/831776#831776

Thanks a lot and regards - Marcel

Re: New IDEA: Create master items (dimensions, measures) in script

Tue, 18 Aug 2015 18:09:38 GMT

Marcel,

Have you taken a look at Jacob Vincent's tool on Branch? Fasttrack QlikView to QlikSense. The API work may already have been completed for you.

Re: New IDEA: Create master items (dimensions, measures) in script

hugmarcel — Wed, 19 Aug 2015 07:25:28 GMT

Hi Jeffrey

yes we know the Fasttrack, we have tried to use it. The Fasttrack evaluates all formulas within a qlikview document. Our KPIs do not exist as formulas, but as variables which are created based on the content of our Excel meta data.

Creating the master items in script would be much more straight forward (for us).

Marcel

Re: New IDEA: Create master items (dimensions, measures) in script

Alexander_Thor — Thu, 20 Aug 2015 07:36:18 GMT

So instead of changing the formula in the library you want to change it in Excel?
Just messing with you, but yes it's something we are thinking about but most likely it won't be done through scripting.

The scripting layer does not real scale well when you want to apply the same measure across multiple apps.

Especially from a maintenance point of view, all your resources are already managed through QMC so introducing another external source makes it really hard to audit the implementation. Not to mention that you might want to propagate changes to a measure without having to reload apps.

Nothing decided, but R&D is thinking about it.

As a workaround, is there a reason you have to use excel? Is the measure dynamically calculated based on input in excel? If not, couldn't any external data store work?

Re: New IDEA: Create master items (dimensions, measures) in script

hugmarcel — Thu, 20 Aug 2015 10:08:16 GMT

Hi Alex

1.) Yes I prefer excel (or another meta data store) over the library.

2.) "... does not real scale well ... accross multiple apps".
Hm, we are using include-files to use same code in different apps. However, reload of script is needed and you would like to avoid this if I understand you correctly.
I do not see a problem when it comes to reloading of apps, as it can be done automatically in task manager.

3.) Any external storage except for excel would be fine.

One of the key strengths of QlikView/Sense is that one gets everything in one box (ETL, Analysis, Webaccess, Security, etc.). There is no need to use any further development area (API, JSON, C#, VStudio...) to use the KEY FUNCTIONALITY. Master Items are one of the key concepts of QSense. I think that adminstration of master items / KEY CONCEPTS should therefore be possible without any further knowledge of third party API tools, etc.

Administration should be straight-forward, lean and easy, and part of the built-in QSense functionality.

Marcel

Re: New IDEA: Create master items (dimensions, measures) in script

Wed, 30 Sep 2015 10:15:30 GMT

We use this way too. Central storage of formula's that are used over multiple reports. Also the added logic of .Lastyear logic and many other generated formula.

I'd really like to be able to script my dimensions and measures so I can preselect certain formula's for my users.

I don't want to manually create measures for different reports.

Re: New IDEA: Create master items (dimensions, measures) in script

Fri, 20 Nov 2015 18:15:20 GMT

Hi Marcel

It's possible to create (from excel) an xml file that Fasttrack can read (without reading from a prj folder).

So you can:

- store metadata in excel

- generate an xml file from excel

- read the xml file from Fasttrack and write master items to Qlik Sense

Re: New IDEA: Create master items (dimensions, measures) in script

rwunderlich — Fri, 20 Nov 2015 19:20:07 GMT

I think the project that Alexander was hinting at is becoming realized.

Architeqt for Qlik Sense

Re: New IDEA: Create master items (dimensions, measures) in script

vadimtsushko — Tue, 24 Nov 2015 06:21:28 GMT

We too use external metadata storage for expressions, labels, comments and so on - using custom text format as described in InQlik Expression Editor in QlikView Deployment Framework environment

I'm currently exploring ways to move that infrastructure to QlikSense. API approach looks promising.

It's relatively easy to implement (not so easy as with load script yet but anyway) and results are good. We can import variables and measures. Hopefully we could add expression syntax check into Sublime Text - that would be nice feature.

While experimenting with variables import though I've noticed one peculiarity: in QlikSense raw number of variables in appplication have serious impact on applpication load time. Observed threshold for me is somewhere about 500 expressions for application. With 1000 expressions application initial load time is in minutes (after the load application works as usual). On the other hand big number of measures do not impact application load time in any considerable way. Application with 1500 measures loads almost as fast as without measures at all.

So I should use some strategy to manage number of variables as low as possible

Re: New IDEA: Create master items (dimensions, measures) in script

Tue, 24 Nov 2015 13:47:40 GMT

Here an example of 2 related xml file and a csv file to use with fasttrack ex1.xml , ex1.xmlscript ex1.csv

ex1.xml

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSheetItems xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SheetItems>
  <Express>
       <Expressions><Exp>Count(Dim1)</Exp><SenseLabel>SenseLabel</SenseLabel><Comment>Measure 0</Comment></Expressions>
       <Expressions><Exp>Count(Dim1)+1</Exp><SenseLabel>Count1</SenseLabel><Comment>Measure 1 Modificata</Comment></Expressions>
       <Expressions><Exp>Count(Dim1)+2</Exp><SenseLabel>Count2</SenseLabel><Comment>Measure 2 Nuova</Comment></Expressions>
       <Expressions><Exp>Count(Dim1)+3</Exp><SenseLabel>Count3</SenseLabel><Comment>Measure 3</Comment></Expressions>
  </Express>
  <DimensionsList>
       <Dimension><Dim>=Dim1&amp;'+0'</Dim><SenseLabel>Dim1+</SenseLabel><Comment>Dimension 0</Comment></Dimension>
       <Dimension><Dim>=Dim1&amp;'+1'</Dim><SenseLabel>Dim1+1</SenseLabel><Comment>Dimension 1</Comment></Dimension>
       <Dimension><Dim>=Dim1&amp;'+2'</Dim><SenseLabel>Dim1+2</SenseLabel><Comment>Dimension 2</Comment></Dimension>
       <Dimension><Dim>=Dim1&amp;'+3'</Dim><SenseLabel>Dim1+3</SenseLabel><Comment>Dimension 3</Comment></Dimension>
  </DimensionsList>
  </SheetItems>
</ArrayOfSheetItems>

ex1.xmlscript

<?xml version="1.0" encoding="utf-8"?>
<script xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <scriptText>///$tab Main
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Directory;
LOAD Dim1, 
     Mis1
FROM
ex1.csv
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

</scriptText>
</script>

ex1.csv

Dim1;Mis1
A;10
B;20

Re: New IDEA: Create master items (dimensions, measures) in script

Michael_Tarallo — Tue, 24 Nov 2015 14:03:50 GMT

Hello All - I was notified of this post from the last update - and it got me thinking about the DERIVE and DECLARE keywords. So I believe it is technically possible to create dimensions (and maybe measures) in this matter today using Qlik Script - I know that this framework is in place and is being improved upon - you can learn more about it here:

Derive: http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/derived-fields.htm

Declare: http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Declare.htm

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Re: New IDEA: Create master items (dimensions, measures) in script

vadimtsushko — Tue, 24 Nov 2015 15:42:07 GMT

Interesting and nice feature, Michael.

But is it related somehow to master items?

Documentation states that derived fields would appear in field list - would they appear also in master items panel and consequently in library?

And I can not see altogether how that feature could be used to create any measure. Could you provide some example for that?

Re: New IDEA: Create master items (dimensions, measures) in script

Michael_Tarallo — Tue, 24 Nov 2015 17:51:46 GMT

I will find out

Re: New IDEA: Create master items (dimensions, measures) in script

Michael_Tarallo — Tue, 24 Nov 2015 18:41:31 GMT

Hi Vadim,

You are correct, I got confirmation - at the moment - derive and declare only populate the field list level and the original intention for the feature is to categorize date / time dimensions. Thought the underling framework could be used for other dimensions, it is not intended to do so yet. There is discussion about auto-populating the master-items list, but it is not on the roadmap at this time.

Regards,

Mike T

Qlik

Re: New IDEA: Create master items (dimensions, measures) in script

vadimtsushko — Tue, 24 Nov 2015 18:49:41 GMT

Thank you for clarification.

Re: New IDEA: Create master items (dimensions, measures) in script

Thu, 03 Dec 2015 16:42:47 GMT

Hi Roberto,

I like this approach and want to test it.I just want to load the XML file with expressions and not load the .xmlscript or the csv. Is it possible? I have my data loaded already thru odbc. When i tried on FastTrack it's not loading .xml but complaining about missing .xmlscript file.

Re: New IDEA: Create master items (dimensions, measures) in script

Thu, 03 Dec 2015 17:06:26 GMT

I will wait for your response on .xmlscript question I had asked in your earlier post but now, I was able to generate the measures and dims using the xml on my Qlik Sense Desktop. Works like a charm and i'm really amazed. I have couple of follow-up questions:

1. Is there a way we can automate this process? Can FastTrack consume an input as webservice etc

2. How to publish dims/meas to server automatically?

Really appreciate your help here! Thank you!

Re: New IDEA: Create master items (dimensions, measures) in script

Fri, 04 Dec 2015 23:00:11 GMT

The .xmlscript is a mandatory file but you Can leave it empty

Inviato da Samsung Mobile.

Re: New IDEA: Create master items (dimensions, measures) in script

Mon, 07 Dec 2015 15:50:33 GMT

Thanks Roberto, will do. I also have few additional questions:

I was able to generate the measures and dims using the xml on my Qlik Sense Desktop. I have couple of follow-up question:

Is there a way to publish master items thru XML to a server? I basically want to automate this process, probably using FastTrack components I want to push XML as webservice or something and load this to a Qlik server. Is it possible?

Really appreciate your help here! Thank you!

Re: New IDEA: Create master items (dimensions, measures) in script

Fri, 18 Dec 2015 16:21:46 GMT

Hi,

I was just wondering if we have an approach to create master items on Qlik Sense server? I'm trying to investigate this further and any help on this subject is much appreciated.