Время прочтения: 6 мин.

В предыдущей статье я описал один из способов организации проекта в QlikView (далее – QV), а также основные методов загрузки данных, используемые в первом слое разработки AppTier1. В данной статье я хочу описать основные отличия синтаксиса в QV от SQL и схемы модели данных.

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

LOAD
	Field1,
	Field2,
	Field3
FROM 
	[C:\Folder\File1.qvd] (qvd)
WHERE 
	Field1 = Condition1 and Field2=Condition2 or Field3=Condition3 

[C:\Folder\File1.qvd] (qvd) – директория расположения файла
Скрипт формирования QVD файла из загруженной в QV таблицы следующий:
STORE tmpData INTO [C:\Folder\File1.qvd] (qvd);

Как мы видим, синтаксис по логике похож на SQL-ский – также указываем какие поля выгружать, откуда и по каким условиям.

Далее опишу основные отличия в синтаксисе от SQL:

  1. Имена таблиц и полей чувствительны к регистру.
  2. Название таблицы, в которую загружаются данные, указывается перед оператором Load:
Table1:
load * from 
[C:\Users\kukhtenkoav\Desktop\UPiO\Qlik\Мониторинг целей\30.06\QVData\DataTier1\Castom_AIM.qvd] (qvd);

Если не прописать название, то оно присвоится по имени файла:

3. Операторы Qualify и Unqualify.

Qualify – делает приписку с названием таблицы перед именем поля:

Qualify *;

Table1:
load * from 
[C:\Users\kukhtenkoav\Desktop\UPiO\Qlik\Мониторинг целей\30.06\QVData\DataTier1\Castom_AIM.qvd] (qvd);

Можно указывать конкретные поля:

Qualify tb_id, gosb_id;

Table1:
load * from 
[C:\Users\kukhtenkoav\Desktop\UPiO\Qlik\Мониторинг целей\30.06\QVData\DataTier1\Castom_AIM.qvd] (qvd);

Unqualify – соответственно наоборот, снимает эту метку.

4. Нет операторов Union и Union All – вместо них используется Concatenate.

5. Нет Case…when…end, вместо него используется if:

if(Field1=1, 'A','B')

Так же применяются вложенные if:

if(Field1=1, 'A', if(Field2=1,'B','C'))

6. Для присвоения значений переменным помимо Set используется Let (отличия в том, что Let производит вычисления, Set – нет):

LET vTable = 't_FCT_BFO_DAILY'

7. Джоины работают так же, как и в SQL, только нет необходимости прописывать поля, по которым происходит соединение.

Table1:
Load * inline [
Key, field1, field2
1, Иван, Иванов
3, Петр, Петров
4, Сергей, Сергеев
];

left join(Table1)
Load * inline [
Key, field3, field4
1, Аудитор, ОАРБ
2, Старший аудитор, ОАКБ
4, Аудитор, ОАРБ
];

Джоин происходит по всем полям с одинаковыми именами в обеих таблицах – в данном случаем по полю Key.

После трансформации данных все QVD файлы загружаются в один QVW и на их основе формируется общая модель данных. Таблицы соединяются между собой при помощи ключей. Как правило, используются две схемы данных:

  1. Звезда:

Основным преимуществом является легкое понимание взаимоотношений между таблицами.

  1. Снежинка:

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

Обращаю внимание, что таблицы соединяются ключами автоматически при наличии одинаковых имен полей. Если одинаковых наименований полей более одного, то формируются синтетические ключи:

Table1:
load * inline[
Key1, Key2, field1, field2
1, 101, Иван, Иванов
3, 103, Петр, Петров
4, 104, Сергей, Сергеев
];

Table2:
load * inline[
Key1, Key2, field3, field4
1, 101, Аудитор, ОАРБ
2, 102, Старший аудитор, ОАКБ
4, 104, Аудитор, ОАРБ
];

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

Для устранения синтетических ключей можно применять несколько методов.

  1. Если таблицы требуется связать по одному полю:

— применение оператора Qualify:

QUALIFY key2;

Table1:
load * inline[
Key1, Key2, field1, field2
1, 101, Иван, Иванов
3, 103, Петр, Петров
4, 104, Сергей, Сергеев
];

Table2:
load * inline[
Key1, Key2, field3, field4
1, 101, Аудитор, ОАРБ
2, 102, Старший аудитор, ОАКБ
4, 104, Аудитор, ОАРБ
];

— переименование столбца при помощи as:

Table1:
load * inline[
Key1, Key2, field1, field2
1, 101, Иван, Иванов
3, 103, Петр, Петров
4, 104, Сергей, Сергеев
];

Table2:
load * inline[
Key1, Key2 as k2, field3, field4
1, 101, Аудитор, ОАРБ
2, 102, Старший аудитор, ОАКБ
4, 104, Аудитор, ОАРБ
];

2. Если таблицы требуется связать по нескольким полям, то создание комбинированного ключа:

-без хэширования:

Table1:
load * inline[
Key1, Key2, field1, field2
1, 101, Иван, Иванов
3, 103, Петр, Петров
4, 104, Сергей, Сергеев
];

Table2:
load * inline[
Key1, Key2, field3, field4
1, 101, Аудитор, ОАРБ
2, 102, Старший аудитор, ОАКБ
4, 104, Аудитор, ОАРБ
];

Table3:
load Key1&Key2 as %key, field1, field2 resident Table1;

drop table Table1;

Table4:
load Key1&Key2 as %key, field3, field4 resident Table2;

drop table Table2;

— с хэшированием:

load autonumberhash128(Key1&Key2) as %key, field1, field2 resident Table1;

    Как мы видим, синтаксис написания скриптов в QV довольно быстро станет понятен для всех, кто работал в SQL, из-за своего сходства с ним. В свою очередь, принцип построения моделей напоминает MS Access, кто работал в нём так же без труда освоит эту часть QV. Но следует помнить о том, что, как и в любом языке, в QV есть свои особенности. В следующей статье я опишу основные объекты визуализации в ответах и расскажу о базовых правилх написания выражений анализа множеств.