Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
hugmarcel
Specialist
Specialist

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

Hi

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

19 Replies
Not applicable

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.

jg

hugmarcel
Specialist
Specialist
Author

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

Alexander_Thor
Employee
Employee

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?

hugmarcel
Specialist
Specialist
Author

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

Not applicable

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.

Not applicable

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Architeqt for Qlik Sense

vadimtsushko
Partner - Creator III
Partner - Creator III

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

Not applicable

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