Qlik Sense Expert

Transforming Hypercube Data - English

2021-08-25T20:02:39.475Z

Here are a couple ways to take HyperCube data and transform it into something more useable.

Example

Qlik Chart Object

This is a simple chart with two dimensions and one measure.

HyperCube Data Sample

The hypercube is the object behind a Qlik chart object. Here is how the chart data is returned by the getHyperCubeData() API call.

Row Array

This would create an array for each row, where each value in the list is a column value. This is a simple version of a hypercube data set.

Object

This would create a single object, where the dimension fields are the key. This is in my opinion the easiest to work with.

Code

/*
    Example:
    Chart has 3 columns (2 Dims, 1 Msr)
    date_num | dim | msr
    ---------|-----|-----
    43556    | B   | 25
    43556    | C   | 26
    ....
*/

data = 

[
  [
    { qText: '43556', qNum: 43556, qElemNumber: 1, qState: 'O' },
    { qText: 'B', qNum: 'NaN', qElemNumber: 1, qState: 'O' },
    { qText: '25', qNum: 25, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43556', qNum: 43556, qElemNumber: 1, qState: 'O' },
    { qText: 'C', qNum: 'NaN', qElemNumber: 2, qState: 'O' },
    { qText: '26', qNum: 26, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43556', qNum: 43556, qElemNumber: 1, qState: 'O' },
    { qText: 'D', qNum: 'NaN', qElemNumber: 0, qState: 'O' },
    { qText: '25', qNum: 25, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43586', qNum: 43586, qElemNumber: 2, qState: 'O' },
    { qText: 'C', qNum: 'NaN', qElemNumber: 2, qState: 'O' },
    { qText: '25', qNum: 25, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43586', qNum: 43586, qElemNumber: 2, qState: 'O' },
    { qText: 'D', qNum: 'NaN', qElemNumber: 0, qState: 'O' },
    { qText: '26', qNum: 26, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43617', qNum: 43617, qElemNumber: 0, qState: 'O' },
    { qText: 'B', qNum: 'NaN', qElemNumber: 1, qState: 'O' },
    { qText: '25', qNum: 25, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43617', qNum: 43617, qElemNumber: 0, qState: 'O' },
    { qText: 'C', qNum: 'NaN', qElemNumber: 2, qState: 'O' },
    { qText: '26', qNum: 26, qElemNumber: 0, qState: 'L' }
  ],
  [
    { qText: '43617', qNum: 43617, qElemNumber: 0, qState: 'O' },
    { qText: 'D', qNum: 'NaN', qElemNumber: 0, qState: 'O' },
    { qText: '26', qNum: 26, qElemNumber: 0, qState: 'L' }
  ]
]
    
const rows = []
const rowObj = {}

for (var r in data) {
   let row = data[r]
    values = []
    v = {}

    for (var c in row) {
        values.push(row[c].qNum== 'NaN' ? row[c].qText : row[c].qNum)
    }

    // Row Array
    rows.push(values)

    // Single Object with dim values as key
    key = values[0]+"|"+values[1]
    rowObj[key] = values[2]

}

console.log('\n','Row Array:', '\n\n', rows,'\n')
console.log('Object:','\n\n', rowObj,'\n')

Initial Article: https://whereclause.com/article/transforming-hypercube-data/

Natively Loading JSON - English

2021-08-25T19:50:43.541Z

I want to start this artcile off by mentioning that this only works for flat json, meaning non-nested json hierarchies. It is possible to leverage this for nested json but takes a bit more elbow grease and knowledge of the schema. Since my intention is to keep these posts brief, I’ll save that for a more in-depth stand alone article.

So getting into it, imagine you have a field with json values and we want to convert the json to a table.

What we can do is leverage From_Field and put json for our table format.

Script

Result

We can also directly load a .json file as well. Although, for it to to work it can only be a single flat json structure with no labels or headers.

Example

Script

Result

I find this tip particularly interesting because it isn’t mentioned in Qlik’s documentation. You would expect json to be one of the options here, but it’s not. Gotta love Easter eggs.

Source of Article: https://whereclause.com/2020/06/23/52-natively-loading-json/

Кейс Set Analysis - Баг или Фича?

2020-12-30T21:04:50.042Z

Вводная часть

Разберем небольшой кейс. Источник данных, собранный на коленке:

SET1:

SET2:

Модель 1

Скрипт загрузки:

SET1:
LOAD
    ID,
    "Field 1",
    1 			As [Flag Set 1]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET1);

SET2:
LOAD
    ID,
    "Field 2",
    1 			As [Flag Set 2]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET2);

Скриншот модели:

Посчитаем количество уникальных ID 4-мя способами:

Count(Distinct ID)
Count({<[Flag Set 1]={1}>} Distinct ID)
Count({<[Flag Set 2]={1}>} Distinct ID)
Count({ <[Flag Set 1]={1}> + <[Flag Set 2]={1}> } Distinct ID)

Результат:

Модель 2

Меняем одну строку:

SET1:
LOAD
    ID,
    "Field 1",
    1 			As [Flag Set 1]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET1);

Outer Join(SET1)
LOAD
    ID,
    "Field 2",
    1 			As [Flag Set 2]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET2);

Получилась модель данных:

Формулы остаются те же, а результат меняется:

Модель 3

Скрипт:

SET1:
LOAD
    ID,
    "Field 1"
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET1);

SET2:
LOAD
    ID,
    "Field 2"
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET2);

//==============================

Dim1:
LOAD
    ID,
    1 			As [Flag Set 1]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET1);

Dim2:
LOAD
    ID,
    1 			As [Flag Set 2]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET2);

Модель данных:

Результат работы:

Модель 4

Скрипт:

SET1:
LOAD
    ID,
    "Field 1"
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET1);

SET2:
LOAD
    ID,
    "Field 2"
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET2);


DIM:
LOAD
    ID,
    1 			As [Flag Set 1]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET1);

Concatenate(DIM)
LOAD
    ID,
    1 			As [Flag Set 2]
FROM [lib://Source/data.xlsx]
(ooxml, embedded labels, table is SET2);

Модель данных:

Результат работы:

JavaScript Export Table to Excel

2020-10-13T16:40:14.085Z

Я сделал подборку библиотек JS, которые пригодятся как для разработки mashup, так и для разработки extensions.

https://tableexport.v5.travismclarke.com/ - The simple, easy-to-implement library to export HTML tables to xlsx, xls, csv, and txt files.
https://github.com/exceljs/exceljs - Read, manipulate and write spreadsheet data and styles to XLSX and JSON. Reverse engineered from Excel spreadsheet files as a project.

https://www.grapecity.com/blogs/how-to-importexport-excel-files-using-javascript-and-spread-sheets - на основе SpreadJS (платная, есть 30 дневная триальная версия)
https://github.com/jmaister/excellentexport - JavaScript export to Excel or CSV. A quick JavaScript library to create export to Excel/CSV from HTML tables in the browser. No server required.
https://bootstrap-table.com/ - Bootstrap Table. An extended table to the integration with some of the most widely used CSS frameworks. (Supports Bootstrap, Semantic UI, Bulma, Material Design, Foundation)
https://redstapler.co/sheetjs-tutorial-create-xlsx/ - SheetJS Tutorial – Create xlsx with Javascript. https://sheetjs.com/ - Read, edit, and export spreadsheets. Works in web browsers and servers. Trusted by Office 365. Платная, сколько стоит не ясно. Есть триальная версия неограниченная по времени, но без стилей.
https://www.npmjs.com/package/exceljs - Read, manipulate and write spreadsheet data and styles to XLSX and JSON. Reverse engineered from Excel spreadsheet files as a project.

https://github.com/ecscstatsconsulting/js-excel-generator - A JavaScript Library for creating Excel Spreadsheets from HTML Tables.
https://www.npmjs.com/package/xlsx-style - Parser and writer for various spreadsheet formats. Pure-JS cleanroom implementation from official specifications and related documents.
https://www.igniteui.com/javascript-excel-library/overview - The Infragistics Ignite UI for jQuery JavaScript Excel library provides the same extensive Excel document object model found in our rich client and server Excel engines right there in the browser (or any JS runtime, like Node).
https://www.geeksforgeeks.org/how-to-convert-html-table-into-excel-spreadsheet-using-jquery/ - Any HTML table that you have created can be converted into an Excel Spreadsheet by using jQuery and it is compatible with all browsers. There are two simple approaches that require basic HTML and jQuery knowledge to achieve this. Разбор примера на основе https://github.com/rainabba/jquery-table2excel
https://www.c-sharpcorner.com/article/reading-a-excel-file-using-html5-jquery/ - In this article, we will learn how to read an Excel file from the client-side and display its contents in an HTML table by making use of the FileReader() API in HTML5 & jQuery.
https://jspreadsheets.com/ - подборка Best JavaScript Data Grids and Spreadsheets
https://medium.com/better-programming/convert-json-to-xlsx-file-in-javascript-e7611a975916 - Convert your JSON into a .xlsx file and save it

Урок 1. Регистрация в Qlik Sense Business Cloud. Как начать работать с Qlik в облачном сервисе?

2020-10-12T21:02:15.291Z

https://t.me/qlik_insight - telegram канал - пишу про Qlik Sense как есть на самом деле, критикую, что-то хвалю, делюсь обучающими материалами.

Урок 1. Регистрация в Qlik Sense Business Cloud. Как начать работать с Qlik в облачном сервисе? В этом уроке будет описан процесс создания аккаунта в облачном сервисе Qlik Sense Business Cloud.

Как скачать актуальную версию Qlik Sense Desktop и войти через Cloud?

2020-10-01T21:46:24.417Z

В этой заметке я покажу, как скачать последнюю версию QS Desktop и авторизоваться под облачной учеткой.

Заходим в ваше облако, либо регистрируемся (доступ триал дается на 30 дней, можно потом смело снова зарегистрироваться, пока клик с множественными регистрациями не борется, к счастью).

Вверху страницы нажимаем кнопку на профиль (см. 1), далее переходим в Settings.

Первая ссылка-кнопка позволяет скачать актуальную версию Qlik Sense Desktop.

Скачайте, установите, запустите. Далее нажмите вторую ссылку, которая свяжет ваш desktop с облаком.

В десктопе появится такая вот кнопка, жмете на нее, далее вбиваете логин и пароль от облака и используете десктоп. После истечения логина снова регистрируетесь, нажимаете на кнопку и у Вас опять активный десктоп.