Easy Excel Style Tables (row-level formulae) in Qlik

[cheenu_janakira]

6-minute video: https://www.youtube.com/watch?v=FQsTiMB9aX4

Files from Github.

Building Excel-styled tables with different formulae on each row has always been a pain point in Qlik.

We have all created Excel styled spreadsheet using Valuelist as a dimension and PICK MATCH in the expression in order to achieve the cell level control as it is in Excel.

However, this becomes hard to read and maintain when expressions in cells get extremely complex and also fidgety when you need to add new lines in an existing front-end Qlik table.

So I looked for an external way of maintaining these and am now sharing the framework and files to be able to easily create, maintain and change these Excel-style tables in Qlik.

Please watch the YouTube video for more ample information, explanation and instructions.

https://youtu.be/PIR9tsPAvjo

[viniamarciano]

Thank you for this @cheenu_janakira . Really helpful. 

One quick question, how do I add more rows? The excel file has 218 editable rows only..

Thanks in advance,

Vinia

[cheenu_janakira]

Hi Vinia,

Just unhide the rows in Excel. There is no limit to the amount of rows you can use. Only if you need more columns, then you need to create them and then add them in the .qvs script.

Cheers,

Cheenu

[Seggy224]

This is brilliant! Really useful and fills a significant gap in Qlik features. Thanks for this.

[Seggy224]

I have managed to convert this into pure script so now can create Excel style tables using script only. Also can display output in Pivot table which means you can add further dimensions using standard Qlik features and so slice and dice the cells into sub-cells! It's great!

[Seggy224]

Further detail on how I have adapted this brilliant work to implement via script rather than by linking to an Excel workbook. (Our configuration does not allow linking in production.)

The first illustration below shows a typical Qlik Sense pivot table except that every cell of it has it's own set analysis formula. These can be further filtered and split.

There is a snag with using a pivot table however (in our April 2019 version) - you cannot stop it sorting whereas a table doesn't have this issue.

The second illustration shows how to use an Excel template I created to program the individual cells, their text format and background colours and then generate the load script.

Happy to share this work.

[bronsonelliott]

@Seggy224 I'd be interested in learning more on how you modified this to have a different formula for each row in a pivot table.

[Seggy224]

Hi
Happy to help. I haven't used Qlik for a couple of years and currently
don't have access (changed job) so I can only work from my own memory.
Are you asking how the whole process works or something particular to pivot
tables versus ordinary tables?
Also - will share these Excels if you can use them.

[image: image.png]

[bronsonelliott]

@Seggy224  Thanks for replying. I have a good idea on how to use the original template with a straight table in Qlik but I now have a use case where I need to create a pivot table where each row has it's own expression and then the rolled up level would have it's own expression.  

So really a way to still have the row-level customization but also with the ability to expand/collapse the view with a summary row that also has it's own expression.

Hopefully that makes sense

[bronsonelliott]

@Seggy224  If you have your excel files that you used for your pivot table example I'd love to view them

[bronsonelliott]

@cheenu_janakira Have you ever applied your row level expression technique to a pivot table in Qlik?