Основы динамического программирования на Qlik Sense. Подпрограммы (Subroutines)

Скрипт Qlik Sense (и Qlik View, они почти одинаковые) интересен тем, что он может не только выполнять явно прописанные команды. Но и генерировать код динамически, который будет себя выполнять.

За это отвечает функционал подпрограмм (subroutines). Подпрограммы представляют собой обычный синтаксис скрипта, обрамленный командами sub и end sub.

Обертывание кода в sub … end sub приводит к тому, что он не выполняется в порядке своего размещения в скрипте, как обычный код. Вместо этого, он выполняется в момент, когда его вызывают с помощью команды Call и указания имени подпрограммы.

Подпрограммы также могут иметь динамические параметры, значения которых передаются при вызове. Такие параметры работают как обычные переменные, но живут они только внутри подпрограммы. Их значения затираются после завершения подпрограммы. При этом внутри подпрограммы можно задавать значения переменных как обычно, через let и set. Значения этих переменных не затираются после выполнения подпрограммы.

Пример: функция удаления всех таблиц кроме указанных

Наверняка вы знаете, что в скрипте загрузки можно удалить ранее загруженную таблицу через команду Drop Tables. Проблема в том, что иногда (особенно на этапе разработки/отладки) нам может потребоваться обратное действие — оставить конкретную таблицу, а остальные удалить. Но для такого сценария в Qlik нет отдельной команды. И все что мы можем сделать — перечислять весь список таблиц кроме той единственной, которую хотим оставить.

Но не все так плохо. Вместо того чтобы писать это руками, мы можем создать подпрограмму, которая будет делать это за нас. Для практики, возьмите любое ваше приложение с моделью данных.

Получаем информацию о таблицах в модели

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

Обозначим начало и конец подпрограммы, а также дадим ей имя, вроде KeepTable.

Что нам нужно, чтобы добиться удаления всех таблиц? Например, прописать drop table для каждой из них. Значит, для начала нам нужен перечень всех таблиц.

В справке Qlik по функциям таблиц приведен прекрасный шаблон кода, который позволяет получить нам список всех таблиц и их полей в модели данных.

//Iterate through the loaded tables
For t = 0 to NoOfTables() - 1

//Iterate through the fields of table
 For f = 1 to NoOfFields(TableName($(t)))
  Tables:
  Load 
   TableName($(t)) as Table,
   TableNumber(TableName($(t))) as TableNo,
   NoOfRows(TableName($(t))) as TableRows,
   FieldName($(f),TableName($(t))) as Field, 
   FieldNumber(FieldName($(f),TableName($(t))),TableName($(t))) as FieldNo
   Autogenerate 1;
 Next f
Next t;

Мы его немного сократим, потому что нам нужны только имена таблиц:

//Iterate through the loaded tables
For t = 0 to NoOfTables() - 1

  Drop_Tables:
  Load 
  TableName($(t)) as Drop_Tables
  Autogenerate 1;
Next t;

Учитывайте, что создаваемые в подпрограмме таблицы взаимодействуют с остальной моделью также, как и таблицы созданные в обычном скрипте. Т.е. таблицы с одинаковым набором полей будут автоматически объединяться. А к таблицам с одинаковыми именами добавляться номера типа Таблица-1, Таблица-2 и т.д. Ваш код должен выглядеть так:

Создайте в конце скрипта пустую вкладку, и вызовите на ней подпрограмму KeepTable.

Как результат, в модели данных появится таблица с единственным полем, содержащим наименование всех таблиц в модели.

Создаем динамический скрипт

Минуточку. Мы вроде собирались массово удалять таблицы, а вместо этого создали еще одну. Какой нам толк от нее?

А толк такой — она станет базой для заготовки автоматически генерируемого кода. Для этого подкрутим ее значения. Что нам нужно писать, когда мы хотим удалить таблицу? Drop Table Название_таблицы;. Названия таблиц у нас уже есть, осталось дописать вокруг них необходимые команды. Код подпрограммы станет таким:

Sub KeepTable

For t = 0 to NoOfTables() - 1
  Drop_Tables:
  Load 
  'drop table ['&TableName($(t))&'];' as Drop_Tables
  Autogenerate 1;
Next

end sub

Обратите внимание, что мы добавляем вокруг названия таблицы текст, причем так, чтобы название таблицы было помещено в квадратные скобки. Когда вы пишете динамический код, который будет обращаться к полям или таблицам, так надо делать всегда. Потому что вы не можете быть уверены, состоит ли название поля/таблицы из одного слова или нескольких. Результат при загрузке будет выглядеть так:

Ок, это выглядит как нужный нам код. Но по факту это просто значение поля в таблице. Как нам заставить его выполниться? На помощь приходят переменные. Особенность переменных в Qlik такова, что их значения можно подставлять абсолютно в любое место скрипта или выражения. Главное, чтобы по итогу получился валидный синтаксис. Для примера: загрузка таблиц над чертой и под чертой даст одинаковый результат.

Хотя код под чертой выглядит как белиберда, еще и подсвечен красным. Просто базовая проверка синтаксиса не может распознать такую комбинацию. Но когда начинается выполнение скрипта, происходит подстановка значений, содержащихся в переменных, и синтаксис становится валидным.

Этот пример наглядно показывает, что Qlik’у реально все равно, куда подставлять переменные. Сейчас мы с помощью переменной заменили часть, отвечающую за загрузку данных (*), и содержащую часть слова inline. Кстати, переменные не имеют присвоенного типа данных (числа/текст/целые и т.д.). К ним проще всего относиться как к бесконечно длинной текстовой строке.

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

Мы остановились на том, что у нас есть набор строк с командами удаления таблиц. Как перенести их в переменную? Сделать это можно с помощью функции peek() (не путайте с pick()). она позволяет обращаться к данным, содержащимся в других таблицах. В т.ч. при задании значения переменной. Эта функция имеет 3 аргумента — название поля, номер строки таблицы (начиная с 0), название таблицы. Прописывается примерно так:

Обратите внимание, что переменную нужно объявлять через let (а не set), потому что только в этом случае функции после знака= будут работать именно как функции. А не как статичный текст, помещаемый в переменную.

Можно пойти разными путями. Например, сагрегировать все drop table … в одну ячейку через функцию concat, и записать ее в переменную, и вызвать переменную в скрипте. А можно пройтись циклом по каждой строке, вызывая каждый drop отдельно. Давайте пойдем по второму сценарию. Код такой:

for i=0 to NoOfRows('Drop_Tables')-1

let vDrop_Tables=peek('Drop_Tables',$(i),'Drop_Tables');
$(vDrop_Tables)

Next

Т.е. цикл отработает по кол-ву строк в таблице Drop_Tables, но со сдвигом нумерации на -1. Это используется для подстановки переменной i в функцию peek(), чтобы брать значение из очередной строки. После объявления переменной, вызываем ее $(vDrop_Tables). Т.к. она содержит текст про удаление таблицы, соответствующая таблица будет удалена. И так для всего списка.

Выполните код. В вашей модели останется только одна таблица: Drop_Tables.

Введение динамических параметров подпрограммы.

Напомню, что изначальная идея была в том, чтобы удалять все таблицы кроме избранных. В этом нам помогут передаваемые при вызове подпрограммы параметры. При объявлении таких параметров они указываются в скобках после названия подпрограммы. Если параметров несколько, они перечисляются через запятую (sub Подпрограмма(Параметр1,Параметр2)).

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

Очевидно, нам нужно передавать в параметрах название таблицы, которую мы хотим оставить. Даже можно чуть интереснее — давайте заложим возможность передавать значения нескольких таблиц. Очевидно, нам нужно прописывать значения через разделитель. В моем примере это будет выглядеть вот так:

Теперь нужно придумать механику, чтобы таблицы с этими названиями не попадали в список таблиц на удаление. Для исключения загрузки полей на основе значений другого поля отлично подойдет команда where not exists(). Нужно только найти способ преобразовать нашу строку со списком таблиц в параметре Keep в таблицу модели данных.

Добиться этого можно с помощью такого кода:

Keep_Tables: load
SubField('$(Keep)',';') as Keep_Tables
AutoGenerate 1;

Параметр Keep будет использован как текстовая строка, значения которой распределятся по полям на основе разделителя «;» через команду subfield().

Т.к. для этой операции мы не обращаемся к таблице-источнику, то используется команда autogenerate 1; К загрузке значений в табилцу Drop_Tables допишем условие:

where not Exists(Keep_Tables,TableName($(t)))

Оно гласит, что загружаться должны только те названия таблиц, которые отсутствуют в поле Keep_Tables. После завершения цикла удаления допишем код очистки с удалением таблицы Drop_Tables и очисткой значений переменных. Таблицу Keep_Tables удалять не надо, она автоматически удалится самой подпрограммой. Т.к. попадет в список существующих таблиц модели данных.

drop tables Drop_Tables;

set vDrop_Tables=;
set t=;
set i=;

Итого, общий код подпрограммы получается таким:

Sub KeepTable(Keep)

Keep_Tables: load
SubField('$(Keep)',';') as Keep_Tables
AutoGenerate 1;


For t = 0 to NoOfTables() - 1
  Drop_Tables:
  Load 
  'drop table ['&TableName($(t))&'];' as Drop_Tables
  Autogenerate 1 where not Exists(Keep_Tables,TableName($(t)));
Next

for i=0 to NoOfRows('Drop_Tables')-1

let vDrop_Tables=peek('Drop_Tables',$(i),'Drop_Tables');
$(vDrop_Tables)

Next

drop tables Drop_Tables;

set vDrop_Tables=;
set t=;
set i=;


end sub

Кстати, насчет очистки переменных. Если при выполнении скрипта последнее значение переменной заданно как пустое, то она не попадет в диспетчер переменных визуального слоя. Но если ранее переменная из скрипта уже попадала в визуальный слой с действительным значением, то она останется в диспетчере. Правда, в ней уже не будет значения и блокировки удаления как у переменной, созданной в скрипте. Можете удалить эти переменные в диспетчере, они больше не появятся.

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

Заключение

Подпрограммы — это мощный инструмент автоматизации написания скриптов и создания собственных функций скрипта загрузки данных.

Потенциал создаваемого функционала ограничивается только вашей фантазией.

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

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