Custom tables with sparklines from scratch using e... - Qlik Community

Francis_Kabinoff · ‎2019-01-11

I’m going to walk through creating a custom table with some sparklines from scratch using Qlik’s open source libraries enigma.js and Picasso.js in just a few steps to show you just how quick and easy it is! This is what the (unpolished but) finished table looks like:

Step 1) Scaffold a project

You’ll need a dev server. I’m going to use ES6 modules, const and let, and arrow functions, but these aren’t requirements. For simplicity, I’ll assume you are using webpack-starter. You can fork or download it at https://github.com/wbkd/webpack-starter.

(Note: You may need Ruby and Python 2.7 installed because webpack-starter uses sass)

If you’re rolling your own, I assume you know what you’re doing. If you’re using webpack-starter then run

npm install

and then

npm start

to boot up your project.

Step 2) Install libraries

You’ll need to install enigma.js and Picasso.js. Just run

npm install --save enigma.js picasso.js

Step 3) Prepare the HTML, CSS, and JS files

In the index.html file, the only thing you want in the body is a blank table element. Simple enough. For the CSS, we’ll add a border to the table, limit the width of the second td element a bit, and add a few styles for what will be our sparkline container. We'll do all the work in a single JavaScript file which we can get ready now by importing the stuff we'll need. Check out the full html and css below, and the imports for the JavaScript file.

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
  <table></table>
</body>
</html>

table, tr, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}

td:nth-child(2) {
  width: 50px;
}

.sparkline {
  position: relative; 
  width: 50px; 
  height: 50px;
  margin-left: 40px;
}

import enigma from 'enigma.js';
import schema from 'enigma.js/schemas/12.20.0.json';
import SenseUtilities from 'enigma.js/sense-utilities';
import picasso from 'picasso.js';
import '../styles/index.scss';

Step 3) Connect to a Qlik Sense app

We’ll connect to the Helpdesk Management demo app. In our JavaScript file we'll need to define a config object which contains the information to connect to a Qlik Sense engine and our app id, and then use SenseUtilities to create a url from the config. Then we can define our session, open it up, and connect to an app.

const config = {
  host: '<host>',
  secure: true,
  port: 443,
  prefix: '',
  appId: '<app id>',
};
const url = SenseUtilities.buildUrl(config);
const session = enigma.create({ schema, url });

session.open().then((global) => global.openDoc(config.appId)).then((doc) => {
  // do stuff here
});

Step 4) Create a hypercube and get data

We want to show a table with a sparkline chart using the Helpdesk Management app, so we can display a table of depratments with the sparkline being a barchart of number of cases by priority, and we'll tack on average case duration just because. We have to define a hypercube and get the data.

// Create Cube
  const qObjectPromise = doc.createSessionObject({
    qInfo: { qType: 'data' },
    qHyperCubeDef: {
      qDimensions: [{
        qDef: { qFieldDefs: ['[Case Owner Group]']}
      }],
      qMeasures: [{
        qDef: { qDef: "Count( {$<Priority={'High'}, Status -={'Closed'} >} Distinct %CaseId )" }
      }, {
        qDef: { qDef: "Count( {$<Priority={'Medium'}, Status -={'Closed'} >} Distinct %CaseId )" }
      }, {
        qDef: { qDef: "Count( {$<Priority={'Low'}, Status -={'Closed'} >} Distinct %CaseId )" }
      }, {
        qDef: { qDef: "Avg([Case Duration Time])" }
      }],
      qInitialDataFetch: [{
        qWidth: 5,
        qHeight: 10,
      }]
    }
  });
  
  // Get Data
  qObjectPromise.then((qObject) => qObject.getLayout()).then((layout) => {
    const { qMatrix } = layout.qHyperCube.qDataPages[0];
    drawTable(qMatrix);
  });

Step 5) Create a table with data

Now we can create the table. Notice in the function to get the data above I call a function called 'drawTable' and pass it the qMatrix. Let's define that function.

// Draw Table function
function drawTable(data) {
  let table = document.querySelector('table');
  let tableContents = '';
  tableContents += `<thead>
                      <tr>
                        <th>Department</th>
                        <th>High/Medium/Low Priority Cases</th>
                        <th>Average Case Duration (days) </th>
                      </tr>
                    </thead>`;
  tableContents += `<tbody>`;
  for (let row of data) {
    tableContents += `<tr>
                        <td>${row[0].qText}</td>
                        <td>
                          <div id='row${row[0].qElemNumber}' class='sparkline'></div>
                        </td>
                        <td>${row[4].qNum}</td>
                      </tr>`;
  }
  tableContents += `</tbody>`;
  table.innerHTML = tableContents;
  
  for (let row of data) {
    drawSparkBars(row[0].qElemNumber, [
      {type: 'matrix', data: [
        ['Priority', 'Number of cases'],
        ['High Priority', row[1].qNum],
        ['Medium Priority', row[2].qNum],
        ['Low Priority', row[3].qNum]
      ]}
    ]);
  }
}

Pretty straightforward. We just build up the html for the table, then add it to the table element. Notice the second td we add a unique id we can reference later and our .sparkline class. This element is where we will add the sparkline for each row. Also notice at the end of the drawTable function we loop through each row and call another function called drawSparkline, with the first parameter being the elementId it should render in, and the second parameter being a data array formatted for Picasso.js. Let's define the drawSparkline function now.

Step 6) Create Picasso.js sparkline

All we have to do in this function is use the elementId and data array passed to it to create a Picasso.js chart. Creating Picasso.js charts is really easy since its just configuration. Below is what this function looks like.

function drawSparkBars(elementId, data) {
  picasso.chart({
    element: document.querySelector(`#row${elementId}`),
    data,
    settings: {
      scales: {
        y: {
          data: { field: 'Number of cases' },
          invert: true,
          include: [0]
        },
        x: { data: { extract: { field: 'Priority' } }, padding: 0.3 },
      },
      components: [{
        key: 'bars',
        type: 'box',
        data: {
          extract: {
            field: 'Priority',
            props: {
              start: 0,
              end: { field: 'Number of cases' }
            }
          }
        },
        settings: {
          major: { scale: 'x' },
          minor: { scale: 'y' },
          box: {
            fill: (d) => { 
              if (d.datum.label === 'High Priority') { return 'red'; }
              if (d.datum.label === 'Medium Priority') { return 'yellow'; } 
              if (d.datum.label === 'Low Priority') { return 'green'; }
            }
          }
        }
      }]
    }
  });
}

And that's it! So in just a few steps you can create a totally custom table with some sparklines from scratch using enigma.js and Picasso.js. Give it a try, and if you have any questions just let me know.

Custom tables with sparklines from scratch using enigma.js and Picasso.js in just a few steps