Часть 2. Знакомство с загрузкой данных

Учебник QlikView (Qlik Sense). Часть 2. Знакомство с загрузкой данных.

Содержание

Понимание структур данных

Операторы загрузки данных

Для загрузки данных используются операторы LOAD или SELECT. Каждый из них создает внутреннюю таблицу. Таблица всегда отображается как список, каждая запись (строка) в которой является новым экземпляром типа объекта, а каждое поле (столбец) — специальным атрибутом или свойством объекта.

Правила

При загрузке данных в программу QlikView применяются следующие правила:

  • Программа QlikView не делает различия между таблицами, созданными операторами LOAD и SELECT. Таким образом, если загружается несколько таблиц, то не имеет значения, загружены они с помощью оператора LOAD или SELECT либо комбинации этих двух операторов.
  • Порядок полей в операторе или исходной таблице базы данных не имеет значения для логики программы QlikView.
  • Имена полей используются в дальнейших процессах для идентификации полей и установления связей. Значения вводятся с учетом регистра, что часто приводит к необходимости переименования полей в скрипте.

Выполнение скрипта

Примерный порядок событий для стандартных операторов LOAD или SELECT показан ниже:

  1. Расчет выражений.
  2. Изменение имен полей с помощью оператора as.
  3. Изменение имен полей с помощью оператора alias.
  4. Уточнение имен полей.
  5. Сопоставление данных, если совпадает имя поля.
  6. Сохранение данных во внутренней таблице.

Поля

Поля представляют собой основной объект, включающий данные, в программе QlikView. Поле, как правило, включает определенное число значений, которые называются значениями поля. В терминологии баз данных можно сказать, что данные, обрабатываемые программой QlikView, поступают из файлов данных. Файл состоит из нескольких полей, в которых каждый элемент данных является записью. Термины «файл», «поле» и «запись» эквивалентны терминам «таблица», «столбец» и «строка» соответственно. Логика AQL QlikView работает только с полями и значениями полей.

Данные полей извлекаются скриптом с помощью операторов LOAD, SELECT или Binary. Единственный способ изменить данные в поле — повторно запустить скрипт. Пользователь не может управлять фактическими значениями полей из макета или посредством автоматизации. После передачи в программу QlikView данные доступны только для просмотра, логических выборок и вычислений.

Значения полей состоят из числовых или буквенно-числовых (текстовых) данных. Числовые значения фактически имеют двойные значения: числовое значение и текущее форматированное текстовое представление. В объектах листа и т. п. отображаются только последние.

Содержимое поля можно представить в списке.

Теги полей

Теги полей позволяют добавлять метаданные в поля в используемой модели данных. Существует два разных типа тегов полей.

  • Системные теги полей

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

  • Пользовательские теги полей

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

Системные теги полей

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

Tag Описание Возможность управления в скрипте
$system

Системное поле, которое создается QlikView во время выполнения скрипта.

См.: Системные поля

Нет
$key Ключевое поле, обеспечивающее связь между двумя или несколькими таблицами. Нет
$keypart Это поле является частью одного или нескольких синтетических ключей. Нет
$syn

Синтетический ключ

См.: Синтетические ключи

Нет

$hidden

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

Можно использовать системные переменные HidePrefixи HideSuffix, чтобы указать, какое поле необходимо скрыть.

Да
$numeric Все (отличные от NULL) значения в поле являются числовыми. Да
$integer Все (отличные от NULL) значения в поле являются целыми числами. Да
$text Никакие значения в поле не являются числовыми. Да
$ascii Значения в поле содержат только стандартные символы ASCII. Да
$date Все (отличные от NULL) значения в поле можно интерпретировать как даты (целые числа). Да
$timestamp Все (отличные от NULL) значения в поле можно интерпретировать как метки времени. Да

Следующие теги, которые пользователь может включать и отключать, задаются в диалоговом окне Свойства документа: Таблицы:

  • $dimension — обозначает поле, рекомендованное для использования в измерениях диаграммы, списках и т. д.
  • $measure — обозначает поле, которое рекомендуется использовать в выражениях.

Системные поля

Помимо полей, извлекаемых из источника данных, системные поля также создает QlikView. Такие поля начинаются со знака «$» и их можно выводить в окнах со списками как и обычные поля. Системные поля, которые обычно создаются во время выполнения скрипта, используются главным образом в качестве дополнительного средства при разработке документа.

Отображение системных полей

Выполните следующие шаги:

  1. Щелкните правой кнопкой мыши лист и выберите параметр Системные поля.
  2. Установите флажок Показать системные поля.

Теперь системные поля доступны как любые другие.

Доступные системные поля

Можно отобразить следующие системные поля:

$Table Отображает все внутренние таблицы, загружаемые скриптом. При выборе отдельной таблицы в области заголовка окна со списком появится символ информации. При нажатии этого символа можно просмотреть таблицу из файла.
$Field Отображает поля, которые считываются из таблиц. Если для этого списка установить Показать частоту на странице Свойства списка: Общие, то можно легко определить ключевые поля, встречающиеся в нескольких внутренних таблицах.
$Fields Числа в этом окне со списком соответствует числу полей в разных таблицах.
$FieldNo В этом окне со списком отображается положение полей в таблицах.
$Rows В этом окне со списком отображается число строк в таблицах.
$Info Если в документе используются информационные таблицы, то в этом окне отобразятся их названия.
Системная таблица

QlikView может автоматически создать сводную таблицу, использующую системные поля. Таблица получает имя System Table и включает два измерения: $Field и $Table, а также выражение only([$Field]). Системная таблица по умолчанию сортируется по частоте. Объект системной таблицы недоступен для клиентов сервера QlikView (AJAX и клиенты Plugin).

Создание системной таблицы

Выполните следующие шаги:

  1. Щелкните правой кнопкой мыши лист и выберите параметр Создать объект листа.
  2. Выберите параметр Системная таблица.

Поля ввода

QlikView поддерживает особый тип поля, которое можно изменить без выполнения скрипта — поле ввода.

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


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

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

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


Вычисляемое поле и ключевые поля не могут использоваться в качестве поля ввода. Функции поля ввода будут автоматически отключены.

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

Можно указать относительное изменение.


Относительное изменение применяется в случае использования режима распределения в качестве части выражения.

Применяется следующий синтаксис (n — число):

%+n увеличивает текущее значение на n%
%-n уменьшает текущее значение на n%
+=n увеличивает текущее значение на n
-=n уменьшает текущее значение на n
*=n умножает текущее значение на n
/=n делит текущее значение на n

Пример:
%+10 увеличивает текущее значение на 10%.
+=56 увеличивает текущее значение на 56.
*=2 умножает текущее значение на 2.
/=2 делит текущее значение на 2.
/=0 не приводит к изменениям.

Для извлечения и установки значений программным путем существуют также API автоматизации.

Группы полей

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

Однако иногда заданная иерархия обеспечивает более эффективное отображение данных. QlikView предлагает возможность задавать группы полей. Группы могут быть иерархическими (детализированными) или неиерархическими (циклическими).

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

Группировать можно любые поля.

Иерархические группы (детализированные)

Если несколько полей формируют естественную иерархию, имеет смысл создать группу детализации. Типичные примеры иерархических групп:

Время: Год, Квартал, Месяц

или

Географическое положение: Континент, Страна, Штат, Город

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

В первом примере "Год" будет использоваться в качестве измерения диаграммы, пока не будет выбран один год. Затем диаграмма будет отображать "Квартал". Если выбран один квартал, диаграмма переключится на "Месяц".

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

Иерархические группы (детализированные)

Если несколько полей формируют естественную иерархию, имеет смысл создать группу детализации. Типичные примеры иерархических групп:

Время: Год, Квартал, Месяц

или

Географическое положение: Континент, Страна, Штат, Город

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

В первом примере Год будет использоваться в качестве измерения диаграммы, пока не будет выбран один год. Затем диаграмма будет отображать Квартал. Если выбран один квартал, диаграмма переключится на Месяц.

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

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

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

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

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

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

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

Логические таблицы

Каждый оператор LOAD или SELECT создает таблицу. Обычно в программе QlikView результаты каждого из этих операторов обрабатываются как одна логическая таблица. Однако из этого правила существует несколько исключений:

  • Если при применении двух или более операторов в полученной таблице содержатся поля с идентичными именами, таблицы будут объединены и будут обрабатываться как одна логическая таблица.
  • Если перед оператором LOAD или SELECT вставлен один из следующих префиксов, данные будут изменяться или обрабатываться по-разному:
concatenate Эта таблица объединяется с другой именованной таблицей (добавляется к ней) или с последней созданной логической таблицей.
crosstable Данная таблица преобразуется из формата перекрестной таблицы в формат столбцов.
generic Данная таблица разделяется на несколько других логических таблиц.
info Данная таблица загружается не как логическая таблица, а как информационная таблица, содержащая ссылки на внешнюю информацию, например файлы, звуки, URL-адреса и так далее.
intervalmatch Таблица (которая должна содержать только два столбца) интерпретируется как числовые интервалы, которые связаны с дискретными числами в указанном поле.
join Данная таблица будет объединена программой QlikView с другой именованной таблицей или последней ранее созданной логической таблицей в области общих полей.
keep Эта таблица уменьшается соответственно общим полям с другой именованной таблицей или с последней созданной логической таблицей.
mapping Эта таблица (которая должна содержать только два столбца) при чтении рассматривается как таблица сопоставлений, которая никогда не связывается с другими таблицами.
semantic Данная таблица загружается не как логическая таблица, а как семантическая таблица, содержащая взаимосвязи, которые не следует объединять, например предшествующий элемент, последующий элемент и другие ссылки на другие объекты того же типа.

После загрузки данных логические таблицы будут связаны.

Имена таблицы

Таблицам QlikView присваиваются имена при сохранении в базе данных QlikView. Имена таблиц можно использовать, например, для операторов LOAD с предложением resident или с выражениями, содержащими функцию peek, а также просматривать в системном поле $Table макета.

Присвоение имен таблицам выполняется по следующим правилам:

  1. Если перед операторами LOAD или SELECT задана метка, то она будет использоваться в качестве имени таблицы. В конце метки должно стоять двоеточие.

    Пример:
    Table1:
    LOAD a,b from c.csv;

    См.: Метки таблицы

  2. Если метка не задана, то сразу после ключевого слова FROM в операторе LOAD или SELECT задается имя файла или таблицы.

    Если имя файла определено в виде знака подстановки, например *.csv, имя приобретает вид tablename-1.

  3. Загруженные встроенные таблицы получают имена INLINExx, где xx — число. Первая встроенная таблица получит имя INLINE01.
  4. Таблицы, создаваемые автоматически, получат имена AUTOGENERATExx, где xx — число. Первая автоматически созданная таблица получит имя AUTOGENERATE01.
  5. Если созданное по описанным выше правилам имя таблицы вступает в конфликт с предыдущим именем таблицы, имя будет дополнено символами -x, где х — число. Число будет увеличиваться до тех пор, пока не будет устранен конфликт. Например, три таблицы могут иметь имя Budget, Budget-1 и Budget-2.

Существует три отдельных домена для имен таблицы: section access, section application и таблицы сопоставления. Имена таблиц, сгенерированные в section access и section application, рассматриваются по отдельности. Если упоминаемое имя таблицы не найдено в разделе, программа QlikView выполняет поиск также и в другом разделе. Таблицы сопоставления обрабатываются отдельно и не имеют связи с двумя другими доменами имен таблиц.

Метки таблицы

Таблицу можно отметить для обращения к ней впоследствии, например, с помощью оператора LOAD с предложением resident или с выражениями, содержащими функцию peek. Метка, которая может представлять собой произвольную строку из цифр и символов, должна располагаться перед первым оператором LOAD или SELECT, который создает таблицу. В конце метки должно стоять двоеточие «:».

Метки, содержащие пробелы, должны заключаться в одинарные или двойные кавычки либо квадратные скобки.

Пример 1:
Table1:
LOAD a,b from c.csv;
LOAD x,y from d.csv where x=peek('a',y,'Table1');

Пример 2: Метка таблицы с пробелом
[All Transactions]:
SELECT * from Transtable;
LOAD Month, sum(Sales) resident [All Transactions] group by Month;

Ассоциации между логическими таблицами

В базе данных может содержаться несколько таблиц. Каждая таблица может быть рассмотрена как список элементов, при этом каждая запись в списке представляет собой экземпляр объекта определенного типа.

Пример:

Если две таблицы являются списками различных элементов, например одна из таблиц является списком клиентов, а другая — списком счетов, и обе таблицы имеют общее поле, например номер клиента, это обычно означает, что между двумя таблицами существует взаимосвязь. В стандартных инструментах запроса SQL две таблицы практически всегда должны быть объединены.

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

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

Сравнение ассоциации QlikView с SQL естественным объединениемouter join

Ассоциация QlikView напоминает SQL естественное объединение outer join. Однако ассоциация имеет более общие свойства: outer join в SQL обычно является односторонней проекцией одной таблицы на другую. Результатом ассоциации всегда является полное (двунаправленное) стандартное объединение outer join.

Информация о частотности в полях для ассоциирования

В использовании большинства полей для ассоциирования существуют некоторые ограничения. Это поля, которые являются общими в двух или нескольких таблицах. Если поле содержится в нескольких таблицах, в программе QlikView возникнет проблема определения таблицы для использования в вычислениях частотности данных.

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

Ограничения для ассоциирования полей
  1. В списке, в котором отображается поле, не может отображаться частота.
  2. Для большинства статистических сущностей окна статистических данных недоступны.
  3. В диаграммах невозможно создавать выражения, содержащие функции, зависимые от данных частоты (такие как функции Sum, Count и значение Average) для поля, пока активен модификатор Distinct. После каждой повторной загрузки все выражения диаграммы в программе QlikView будут исследованы на предмет наличия неоднозначных случаев, возникших в результате изменений структур данных. Если будут найдены неоднозначные выражения, на экране отобразится предупреждение и это выражение будет деактивировано. Пока проблема не будет устранена, это выражение невозможно снова активировать. Если активирован файл журнала, все неоднозначные выражения будут включены в файл журнала.
Обходной прием

Существует простой способ преодоления этих ограничений. Еще раз загрузите поле из таблицы, в которой должно быть выполнено вычисление частот, и сохраните его с новым именем. Затем новое поле можно использовать для создания списка частот, статистических данных или для вычислений в диаграммах.

Синтетические ключи

Если две или несколько внутренних таблиц имеют два или несколько общих полей, то это предполагает взаимосвязь составного ключа. В программе QlikView эта операция выполняется с помощью создания синтетических ключей автоматически. Эти ключи представляют собой анонимные поля, включающие все возможные сочетания составного ключа.

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

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

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

Работа с синтетическими ключами

Проблему с появлением синтетических ключей в скрипте загрузки можно решить несколькими способами:

  • Убедитесь, что в качестве ключей используются только поля, логически связывающие две таблицы.

    • Такие поля, как «Comment», «Remark» и «Description» могут находиться в нескольких таблицах без связи с чем-либо, и, таким образом, не должны использоваться как ключи.
    • Такие поля, как «Date», «Company» и «Name» могут находиться в нескольких таблицах и иметь одинаковые значения, но, тем не менее, разные роли (Дата заказа/Дата отправки, Компания клиента/Компания поставщика). В таких случаях они не должны использоваться как ключи.
  • Убедитесь, что не используются лишние поля, только необходимые. Например при использовании даты в качестве ключа необходимо убедиться, что поля «year», «month» или «day_of_month», относящиеся к одной дате, загружены не более чем из одной внутренней таблицы.

  • При необходимости формируйте собственные несоставные ключи. Обычно это выполняется путем объединения строк внутри функции скрипта AutoNumber.

Типы данных в QlikView

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

Представление данных внутри программы QlikView

Чтобы понять интерпретацию данных и форматирование чисел в программе QlikView, необходимо знать, каким образом хранятся данные внутри программы. Все данные, загруженные в программу QlikView, доступны в двух представлениях: в виде строки или числа.

  1. Строковое представление доступно всегда и представляет собой то, что отображается в списках и других объектах листа. Форматирование данных в списках (числовой формат) влияет только на строковое представление.
  2. Числовое представление доступно только в том случае, если данные можно интерпретировать как действительное число. Числовое представление используется для всех числовых расчетов и числовой сортировки.

Если несколько элементов данных, считываемых в одно поле, имеют одинаковое числовое представление, то они будут рассматриваться как одно и то же значение и все использовать первое обнаруженное строковое представление. Пример: числа 1,0, 1 и 1,000, считываемые в указанном порядке, будут иметь числовое представление 1 и исходное строковое представление 1.0.

Интерпретация чисел

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

Данные со сведениями о типе

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

QlikView запомнит первоначальный числовой формат поля, даже если формат числа изменен в диалоговых окнах формата числа приложения. Можно всегда восстановить первоначальный формат, нажав кнопку Значение по умолчанию в диалоговых окнах формата числа.

Далее перечислены форматы по умолчанию для различных типов данных:

  • целые числа, числа с плавающей точкой: настройка по умолчанию для числа
  • валюта: настройка по умолчанию для валюты
  • время, дата, метка времени: форматирование стандарта ISO

Настройки по умолчанию для чисел и валют определяются с помощью переменных интерпретации чисел скрипта или в настройках операционной системы (панель управления).

См.: Переменные интерпретации числа

Данные без сведений о типе

Для данных без определенной информации о форматировании из источника (например, данные из текстовых файлов или данные ODBC с общим форматом) ситуация становится более запутанной. Конечный результат будет зависеть как минимум от шести различных факторов:

  1. Способ записи данных в базу данных источника
  2. Настройки операционной системы для числа, даты, времени, даты и т. п. (Панель управления)
  3. Использование дополнительных переменных интерпретации чисел в скрипте
  4. Использование дополнительных функций интерпретации в скрипте
  5. использования дополнительных функций форматирования в скрипте;
  6. элементов управления форматированием чисел в документе.

Программа QlikView пытается интерпретировать входные данные как число, дату, время и т. д. Если в данных используются системные настройки по умолчанию, интерпретация и форматирование отображения выполняется в программе QlikView автоматически, и пользователю не нужно изменять скрипт или какие-либо настройки в программе QlikView. Существует простой способ определения, правильно ли были интерпретированы входные данные: числовые значения выравниваются в списках по правом краю, а текстовые строки – по левому.

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

Программа QlikView будет интерпретировать данные как:

  1. Число в соответствии с форматом по умолчанию для чисел.
  2. Дата в соответствии с форматом по умолчанию для даты.
  3. Метка времени в соответствии с форматом по умолчанию для времени и даты.
  4. Время в соответствии с форматом по умолчанию для времени.
  5. Дата приводится в соответствии со следующим форматом: yyyy-MM-dd.
  6. Метка времени приводится в соответствии со следующим форматом: YYYY-MM-DD hh:mm[:ss[.fff]].
  7. Время приводится в соответствии со следующим форматом: hh:mm[:ss[.fff]].
  8. Деньги приводятся в соответствии с форматом по умолчанию для валюты.
  9. Число с элементом '.' в качестве символа для разделения десятичных разрядов и ',' в качестве разделителя тысяч при условии, что ни для десятичного разделителя, ни для разделителя тысяч не указан знак ','.
  10. Число с элементом ',' в качестве символа для разделения десятичных разрядов и ',' в качестве разделителя тысяч при условии, что ни для десятичного разделителя, ни для разделителя тысяч не указан знак '.'.
  11. Текстовая строка. Данная последняя проверка никогда не имеет отрицательного результата: если данные можно считать, то их всегда можно интерпретировать как строку.

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

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

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

Интерпретация даты и времени

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

Серийный номер даты – это (действительное) число дней, прошедших с 30 декабря 1899 г., т. е. формат QlikView идентичен системе дат 1900, которая используется в Microsoft Excel и других программах, в диапазоне от 1 марта 1900 г. до 28 февраля 2100 г. Например, 33857 соответствует 10 сентября 1992 г. За пределами этого диапазона программа QlikView использует ту же систему дат, расширенную до григорианского календаря.

Серийный номер для значения времени представляет собой число от 0 до 1. Серийный номер 0.00000 соответствует 00:00:00, а 0.99999 соответствует 23:59:59. Смешанные числа указывают дату и время: серийный номер 2.5 означает 1 января 1900 г., полдень.

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

См.: Переменные интерпретации числа

См.: Функции форматирования

Пример 1:

1997–08–06 хранится в виде: 35648
09:00 хранится в виде: 0.375
1997–08–06 09:00 хранится в виде: 35648.375

и наоборот.

35648 в числовом формате 'D/M/YY' отображается как: 6/8/97
0.375 в числовом формате 'hh.mm' отображается как: 09.00

Программа QlikView следует установленным правилам при попытке интерпретации дат, времени и данных других типов. Однако окончательный результат будет зависеть от нескольких факторов, как описано выше.

Пример 2:

В этих примерах предполагаются следующие настройки по умолчанию:

  • Десятичный разделитель числа: .
  • Краткий формат даты: YY-MM-DD
  • Формат времени: hh:mm

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

Исходные данные Интерпретация программы QlikView по умолчанию Формат даты 'YYYY-MM-DD' Формат даты 'MM/DD/YYYY' Формат времени 'hh:mm' Формат числа '# ##0.00'
0.375 0.375 1899–12–30 12/30/1899 09:00 0.38
33857 33857 1992–09–10 09/10/1992 00:00 33 857.00
97–08–06 97–08–06 1997–08–06 08/06/1997 00:00 35 648.00
970806 970806 4557–12–21 12/21/4557 00:00 970 806.00
8/6/97 8/6/97 8/6/97 8/6/97 8/6/97 8/6/97

В следующей таблице показаны различные представления, когда данные считываются в программу QlikView с помощью функции интерпретации в скрипте date#( A, 'M/D/YY'):

Исходные данные Интерпретация программы QlikView по умолчанию Формат даты 'YYYY-MM-DD' Формат даты 'MM/DD/YYYY' Формат времени 'hh:mm' Формат числа '# ##0.00'
0.375 0.375 0.375 0.375 0.375 0.375
33857 33857 33857 33857 33857 33857
97–08–06 97–08–06 97–08–06 97–08–06 97–08–06 97–08–06
970806 970806 970806 970806 970806 970806
8/6/97 8/6/97 1997–08–06 08/06/1997 00:00 35 648.00

Инструкции по данным и полям

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

Инструкции по количеству загруженных данных

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

Верхние пределы для таблиц данных и полей

Обратите внимание, что при построении очень больших документов в документе QlikView не может содержаться больше 2 147 483 648 отдельных значений в одном поле.

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

Рекомендуемое ограничение для разделов загрузки скрипта

Рекомендуемое максимальное число символов для использования в одном разделе скрипта загрузки составляет 50 000 символов.

Условия для форматов числа и времени

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

Форматы чисел
  • Для обозначения определенного числа разрядов используется символ "0" для каждого разряда.
  • Для обозначения возможного разряда используйте символ "#". Если формат содержит только символы # слева от десятичного разделителя и числа меньше 1, нули отбрасываются и числа начинаются с десятичного разделителя. Если формат содержит символы # справа от десятичного разделителя, будут отображены все значения.
  • Чтобы отметить позицию разделителя тысяч или десятичного разделителя, используйте соответствующий разделитель тысяч и десятичный разделитель.

Код формата используется для определения позиций разделителей. Невозможно установить разделитель в коде формата. Для этого используйте переменныеDecimalSep и ThousandSep в скрипте.

Можно использовать разделитель тысяч для группировки цифр по любому числу позиций. Например, строка формата "0000–0000–0000" (разделитель тысяч="-") может использоваться для отображения двенадцатиразрядного номера компонента, такого как "0012–4567–8912".

См.: DecimalSep и ThousandSep

Примеры:

# ##0

описывает число как целое с разделителем тысяч.

###0

описывает число как целое без разделителя тысяч.

0000 описывает число как целое не менее чем с четырьмя разрядами. Например, число 123 будет отображаться как 0123.
0.000

описывает число с тремя десятичными знаками.

0.0##

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

Специальные форматы чисел

Программа QlikView может интерпретировать и форматировать числа с любым основанием от 2 до 36, включая двоичные, восьмеричные и шестнадцатеричные числа. Также поддерживаются римские форматы.

Двоичный формат

Чтобы указать двоичный формат, код формата должен начинаться с (bin) или (BIN).

Восьмеричный формат

Чтобы указать восьмеричный формат, код формата должен начинаться с (oct) или (OCT).

Шестнадцатеричный формат

Чтобы указать шестнадцатеричный формат, код формата должен начинаться с (hex) или (HEX). При использовании прописных букв для форматирования будут использоваться буквы A-F (например, 14FA). При использовании строчных букв для форматирования будут использоваться буквы a-f (например, 14fa). Интерпретация будет работать для обоих вариантов независимо от регистра букв кода формата.

Десятичный формат

Использование элементов (dec) или (DEC) для обозначения десятичного формата допускается, но это излишне.

Настраиваемый формат основания

Для указания формата с любым основанием от 2 до 36 код формата должен начинаться с (rxx) или (Rxx), где xx — это двухзначное число, обозначающее используемое основание. Если используется прописная буква R, буквы в основаниях более 10 будут изменены на прописные при выполнении форматирования программой QlikView (например, 14FA). При использовании строчных букв для форматирования будут использоваться строчные буквы (например, 14fa). Интерпретация будет работать для обоих вариантов независимо от регистра букв кода формата. Необходимо иметь в виду, что элемент (r02) эквивалентен элементу (bin), элемент (R16) эквивалентен элементу (HEX) и т. д.

Римский формат

Чтобы указать римские числа, код формата должен начинаться с элемента (rom) или (ROM). При использовании прописных букв для форматирования будут использоваться прописные буквы (например, MMXVI). При использовании строчных букв для форматирования будут использоваться строчные буквы (например, mmxvi). Интерпретация будет работать для обоих вариантов независимо от регистра букв кода формата. Римские цифры обобщаются со знаком минус для отрицательных чисел, и 0 для нуля. При использовании римского форматирования десятичные дроби игнорируются.

Примеры:

num(199, '(bin)') возвращает 11000111
num(199, '(oct)') возвращает 307
num(199, '(hex)') возвращает c7
num(199, '(HEX)' ) возвращает C7
num(199, '(r02)' ) возвращает 11000111
num(199, '(r16)') возвращает c7
num(199, '(R16)' ) возвращает C7
num(199, '(R36)') возвращает 5J
num(199, '(rom)') возвращает cxcix
num(199, '(ROM)' ) возвращает CXCIX
Даты

Для форматирования даты можно использовать следующие символы. Можно использовать произвольные разделители.

D

Для описания дня используйте символ "D" для каждого разряда.

M

Для описания числа месяца используйте символ "M".

  • Для одного или двух разрядов используйте символы "M" или "MM".
  • Символ "MMM" обозначает короткое название месяца в буквах, как определено операционной системой или переопределено системной переменной MonthNames в скрипте.
  • Символ "MMMM" обозначает длинное название месяца в буквах, как определено операционной системой или переопределено системной переменной LongMonthNames в скрипте.

См.: MonthNames и LongMonthNames

Y

Для описания года используйте символ "Y" для каждого разряда.

W

Для описания для недели используйте символ "W".

  • Символ "W" возвратит число дня (например, 0 для понедельника) как одноразрядное число.
  • Символ "WW" возвратит число с двумя разрядами (например, 02 для среды).
  • Символ "WWW" отобразит короткую версию названия дня недели (например, пон), как определено операционной системой или переопределено системной переменной DayNames в скрипте.
  • Символ "WWWW" отобразит длинную версию названия дня недели (например, понедельник), как определено операционной системой или переопределено системной переменной LongDayNames в скрипте.

См.: DayNames и LongDayNames

Примеры: (31 марта 2013 года в качестве даты для примера)

YY-MM-DD описывает дату как 13–03–31.
YYYY-MM-DD описывает дату как 3/31/13.
YYYY-MMM-DD описывает дату как 2013-март-31.
DD MMMM YYYY описывает дату как 31 марта 2013 г.
M/D/YY описывает дату как 3/31/13.
W YY-MM-DD описывает дату как 6 13–03–31.
WWW YY-MM-DD описывает дату как сб 13–03–31.
WWWW YY-MM-DD описывает дату как вскр 13–03–31.
Время

Для форматирования времени можно использовать следующие символы. Можно использовать произвольные разделители.

h

Для описания часов используйте символ "h" для каждого разряда.

m

Для описания минут используйте символ "m" для каждого разряда.

s

Для описания секунд используйте символ "s" для каждого разряда.

f Для описания долей секунды используйте символ "f" для каждого разряда.
tt

Для описания времени в формате AM/PM используйте символ "tt" после времени.

Примеры: (18:30 в качестве времени для примера):

hh:mm описывает время как 18:30
hh.mm.ss.ff описывает время как 18.30.00.00
hh:mm:tt описывает время как 06:30:pm
Метки времени

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

Примеры: (31 марта 2013 года 18:30 в качестве метки времени для примера):

YY-MM-DD hh:mm описывает метку времени как 13–03–31 18:30.
M/D/Y hh.mm.ss.ffff описывает метку времени как 3/31/13 18.30.00.0000

Загрузка данных из файлов

QlikView может считывать данные из файлов, представляющих собой таблицы, в которых поля разделены такими разделителями, как запятые, знаки табуляции или точки с запятой. Другие допустимые форматы — файлы dif (Data Interchange Format), fix (fixed record length — ограниченная длина записи), таблицы HTML, файлы Excel, файлы xml, файлы в собственном формате QVD и файлы QVX. В большинстве случаев первая строка файла содержит имена полей.

Файлы загружаются с помощью оператора load в скрипте.

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

В операторах load можно использовать полный набор выражений скрипта.

Чтобы считать данные из другого документа QlikView, используйте оператор binary.

Табличный файл

В программе QlikView Табличный файл означает файл с таблицей, в которой поля разделены, например, запятыми, табуляторами и точками с запятой (другое название — текстовый файл), либо у полей ограничена длина. В первой строке обычно содержатся имена полей. К табличным файлам относятся файлы dif (Data Interchange Format), fix (fixed record length — ограниченная длина записи), таблицы HTML и файлы Excel.


Как подготовить файлы Excel для загрузки в программу QlikView

Если необходимо загрузить файлы Microsoft Excel в программу QlikView, есть много функций, которые можно использовать для преобразования и очищения данных в скрипте загрузки, но, возможно, удобнее подготовить исходные данные непосредственно в табличном файле Microsoft Excel. В этом разделе предложено несколько подсказок, как подготовить таблицу для ее загрузки в программу QlikView с минимальным кодированием скрипта.

Используйте заголовки столбцов

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

Форматирование данных

Файл Excel легче загрузить в программу QlikView, если его содержимое организовано в виде необработанных данных в таблице. Желательно избегать следующего:

  • Агрегированные величины, такие как суммы и итоги. Агрегированные величины можно определить и вычислить в программе QlikView.
  • Дубликаты заголовков.
  • Дополнительная информация, которая не является частью данных, например комментарии. Лучше всего иметь отдельный столбец для комментариев, который можно пропустить при загрузке файла в программу QlikView.
  • Макет данных перекрестной таблицы. Если, например, есть по одному столбцу для каждого месяца, лучше создать один столбец с именем «Месяц» и записать те же данные в 12 строк, по одной строке для каждого месяца. В дальнейшем вы всегда сможете просмотреть этот столбец в формате перекрестной таблицы в программе QlikView.
  • Промежуточные заголовки, например, строка с названием «Отдел А» с последующими строками, относящимися к Отделу А. Вместо этого следует создать столбец с именем «Отдел» и заполнить ее соответствующими названиями отделов.
  • Объединенные ячейки. Лучше помещать каждое значение ячейки отдельно.
  • Пустые ячейки, значение которых подразумевается вышеуказанными значениями. Заполните все пустые ячейки, чтобы каждая из них содержала данные.

Используйте именованные области

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

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

Удалите защиту паролем

Перед загрузкой данных рекомендуется удалить защиту паролем файла Excel.

Загрузка данных из баз данных

Данные из коммерческих баз данных загружаются в QlikView с помощью интерфейса Microsoft OLE DB/ODBC. Для этого необходимо установить драйвер, поддерживающий соответствующую СУБД, и настроить базу данных в качестве источника данных ODBC.

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

Теперь следует задать поля и таблицы, подлежащие загрузке, в операторе Select. Чтобы создать этот оператор, нажмите кнопку Выбрать.

См.: Создать выражение для SELECT

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

Также агрегированные результаты выполнения запроса из больших наборов данных без их загрузки в модель данных QlikView можно загрузить с помощью функции Direct Discovery.

См.: Direct Discovery

Логика в базах данных

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

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

При выборе значений из нескольких полей, предполагается использование логической функции AND.

При выборе нескольких значений одного поля предполагается использование логической функции OR.

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

Если необходимо отображать информацию о поле, добавьте в скрипт таблицу, состоящую из двух столбцов. Первый столбец таблицы должен содержать значения поля, второй — набор информации, связанной со значением поля. В заголовке первого столбца необходимо указать имя поля. Специальный синтаксис скрипта оператора LOAD/SELECT определяет, QlikView что данная таблица будет обработана как информационная.

Переименование полей

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

  1. Два поля имеют разные имена, хотя обозначают одно и то же:
    • Поле ID в таблице Customers
    • Поле CustomerID в таблице Orders

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

  2. Два поля имеют одинаковые имена, но обозначают разные элементы:
    • Поле Date в таблице Invoices
    • Поле Date в таблице Orders

    Эти два поля желательно переименовать, например: InvoiceDate и OrderDate.

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

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

Пример 1: Использование оператора alias

Оператор LOAD или SELECT может стоять после оператора alias.

Alias ID as CustomerID;
LOAD * from Customer.csv;

Пример 2: Использование спецификатора as

Оператор LOAD или SELECT может содержать спецификатор as.

LOAD ID as CustomerID, Name, Address, Zip, City, State from Customer.csv;

Объединение таблиц

Автоматическое объединение

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

Пример:
LOAD a, b, c from table1.csv;
LOAD a, c, b from table2.csv;

В получившейся в результате внутренней таблице содержатся поля a, b и c. Количество записей является суммой количества записей в таблице 1 и таблице 2.

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

Принудительное объединение

Даже если набор полей в двух и более таблицах совпадает не полностью, в программе QlikView все равно можно объединить эти две таблицы. Это выполняется с помощью префикса concatenate в скрипте, который объединяет таблицу с другой указанной таблицей или с последней созданной таблицей.

Пример:
LOAD a, b, c from table1.csv;
concatenate LOAD a, c from table2,csv;

В получившейся в результате внутренней таблице содержатся поля a, b и c. Количество записей в получившейся таблице является суммой количества записей в таблице 1 и таблице 2. Значением поля b в записях, полученных из таблицы 2, является NULL.

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

Предотвращение объединения

Если имена полей и число полей в двух и более загруженных таблицах точно совпадают, то программа QlikView автоматически объединит содержимое различных операторов в одну таблицу. Это можно предотвратить с помощью оператора noconcatenate. Таблица, загруженная с помощью соответствующего оператора LOAD или SELECT, в последствии не будет объединена с существующей таблицей.

Пример:
LOAD a, b, c from table1.csv;
noconcatenate LOAD a, b, c from table2.csv;

Загрузка данных из ранее загруженной таблицы

Можно использовать логическое условие Resident в операторе LOAD, чтобы загрузить данные из ранее загруженной таблицы. Это удобно, когда необходимо выполнить вычисления с данными, загруженными с помощью оператора SELECT, когда нет возможности использовать функции QlikView, такие как обработка даты или числовых значений.

Пример:

В этом примере интерпретация даты выполняется в загрузке Resident, поскольку ее выполнение в исходной загрузке Crosstable LOAD невозможно.

PreBudget:
Crosstable (Month, Amount, 1)
LOAD Account,
   Jan,
   Feb,
   Mar,

From Budget;

Budget:
Noconcatenate
LOAD
   Account,
   Month(Date#(Month,’MMM’)) as Month,
   Amount
   Resident PreBudget;

Drop Table PreBudget;

Часто элемент Resident используется, при необходимости временной таблицы для выполнения вычислений или фильтра. Если временная таблица больше не нужна, избавьтесь от нее с помощью оператора Drop table.

Элемент Resident или предшествующий элемент LOAD?

В большинстве случаев тот же результат можно достичь с помощью предшествующего оператора LOAD. Именно оператор LOAD выполняет загрузку из оператора LOAD или SELECT ниже, не указывая префикс источника, такой как From или Resident, который обычно указывается. Предшествующий оператор LOAD — это обычно более быстрый способ, но есть случаи, когда необходимо использовать Resident LOAD:

  • В случае необходимости использования утверждения Order_by для сортировки записей перед обработкой оператора LOAD.
  • В случае необходимости использования одного из следующих префиксов. В этом случае предшествующий элемент LOAD не поддерживается:
    • Crosstable
    • Join
    • Intervalmatch

Предшествующий оператор LOAD

Предшествующий оператор LOAD позволяет загружать таблицу одной операцией, но, тем не менее, определяют несколько последующих преобразований. По существу, именно оператор LOAD выполняет загрузку из нижеуказанных операторов LOAD или SELECT, не указывая префикс источника, такой как From или Resident, который обычно указывается. Таким образом, можно выстроить в столбик любое количество операторов LOAD. Сначала будет оценен самый нижний оператор, затем оператор над ним и так до самого верхнего оператора.

Тот же результат можно достичь с помощью оператора Resident, но в большинстве случаев с предшествующим оператором LOAD это будет быстрее.

Еще одним преимуществом предшествующего оператора load является возможность хранить вычисление в одном месте и использовать его в операторах LOAD, размещенных выше.

Следующие префиксы невозможно использовать вместе с предшествующим оператором LOAD:Join, Crosstable и Intervalmatch.

Пример 1: Преобразование данных, загруженных оператором SELECT

В случае загрузки данных из базы данных с помощью оператора SELECT нельзя использовать функции QlikView для интерпретации данных в операторе SELECT. В таком случае необходимо добавить оператор LOAD, в котором выполняется преобразование данных, над оператором SELECT.

В этом примере интерпретируется дата, сохраненная в виде строки, с помощью функции QlikView Date# в операторе LOAD. При этом предшествующий оператор SELECT используется в качестве исходного.

LOAD Date#(OrderDate,’YYYYMMDD’) as OrderDate;
SQL SELECT OrderDate FROM … ;

Пример 2: Упрощение скрипта с помощью многократного использования вычислений

В этом примере вычисление в скрипте используется несколько раз:

LOAD …,
   Age( FromDate + IterNo() – 1, BirthDate ) as Age,
   Date( FromDate + IterNo() – 1 ) as ReferenceDate
   Resident Policies
      While IterNo() <= ToDate — FromDate + 1 ;

Использовав это вычисление однажды, его можно использовать повторно в функции Age в предшествующем операторе LOAD:

LOAD …, ReferenceDate,
   Age( ReferenceDate, BirthDate ) as Age;
LOAD *,
   Date( FromDate + IterNo() – 1 ) as ReferenceDate
   Resident Policies
      While IterNo() <= ToDate — FromDate + 1 ;

Частичная загрузка

Эта команда выполняет только операторы Load и Select, перед которыми стоит префикс Replace или Add. Команда не влияет на другие таблицы данных.

Расширения со знаком доллара

Расширения со знаком доллара представляют собой определения текстовых замещений, используемых в скрипте или в выражениях. Этот процесс известен как расширение — даже в том случае, когда новый текст становится меньше. Замещение выполняется непосредственно перед оценкой оператора скрипта или выражения. Технически это расширение макроса.

Расширение всегда начинается с '$(' и заканчивается ') ', а содержимое в скобках определяет то, как будет происходить текстовое замещение. Во избежание путаницы с макросами скрипта далее мы будем обращаться к расширениям макроса как к расширениям со знаком доллара.

Расширения со знаком доллара могут использоваться со следующими элементами:

  • переменные
  • параметры
  • выражения
Расширение со знаком доллара ограничено количеством расширений, которое оно может вычислять. Любое расширение более 1000 вычисляться не будет.

Расширение со знаком доллара с использованием переменной

При использовании переменной для текстового замещения в скрипте или в выражении используется следующий синтаксис:

$(variablename)

$(variablename) расширяется до значения в переменной. Если элемент variablename не существует, расширение будет пустой строкой.

Для расширений числовых переменных используется следующий синтаксис:

$(#variablename)

Он всегда возвращает допустимое представление десятичной точки числового значения переменной, возможно, в экспоненциальной нотации (для очень больших/малых чисел). Если элемент variablename не существует или не содержит числовое значение, вместо этого будет выполнено расширение до 0.

Пример:
SET DecimalSep=',';
LET X = 7/2;

Расширение со знаком доллара $(X) будет расширяться до 3,5, в то время как $(#X) будет расширяться до 3.5.

Пример:
Set Mypath=C:\MyDocs\Files\;

LOAD * from $(MyPath)abc.csv;

Данные будут загружены из файла по пути C:\MyDocs\Files\abc.csv.

Пример:
Set CurrentYear=1992;

SQL SELECT * FROM table1 WHERE Year=$(CurrentYear);

Строки с элементом Year=1992 будут выбраны.

Пример:
Set vConcatenate = ;
For each vFile in FileList('.\*.txt')
   Data:
   $(vConcatenate)
   LOAD * FROM [$(vFile)];
   Set vConcatenate = Concatenate ;
Next vFile

В этом примере все файлы .txt в каталоге загружаются с помощью префикса Concatenate. Это может потребоваться, если поля отличаются незначительно, так как в этом случае автоматическое объединение не работает. Переменная vConcatenate изначально задается в пустую строку, поскольку префикс Concatenate не используется при первой загрузке. Если каталог содержит три файла под именами file1.txt, file2.txt и file3.txt, оператор LOAD за время трех итераций расширится до:

LOAD * FROM[.\file1.txt];
Concatenate LOAD * FROM[.\file2.txt];
Concatenate LOAD * FROM[.\file3.txt];

Расширения со знаком доллара с использованием параметров

В расширениях со знаком доллара могут использоваться параметры. В этом случае переменная должна содержать такие формальные параметры, как $1, $2, $3 и т. д. При расширении переменной параметры должны указываться в списке, разделенном запятой.

Пример:
Set MUL=’$1*$2’;
Set X=$(MUL(3,7)); // возвращает '3*7' в элементе X
Let X=$(MUL(3,7)); // возвращает 21 в элементе X

Если число формальных параметров превышает число действительных параметров, будут расширены только те формальные параметры, которые соответствуют действительным параметрам. Если число действительных параметров превышает число формальных параметров, излишние действительные параметры будут проигнорированы.

Пример:
Set MUL=’$1*$2’;
Set X=$(MUL); // возвращает '$1*$2' в элементе X
Set X=$(MUL(10)); // возвращает '10*$2' в элементе X
Let X=$(MUL(5,7,8)); // возвращает 35 в элементе X

Параметр $0 возвращает число параметров, фактически переданных с помощью вызова.

Пример:
set MUL='$1*$2 $0 par';
set X=$(MUL(3,7)); // возвращает '3*7 2 par' в элементе X

Расширение со знаком доллара с использованием выражения

В расширениях со знаком доллара могут использоваться выражения. В таком случае содержимое в скобках должно начинаться со знака «равно»:

$(=expression )

Выражение будет оценено, а значение будет использовано в расширении.

Пример:
$(=Year(Today())); // возвращает строку с текущим годом.
$(=Only(Year)-1); // возвращает год на единицу раньше выбранного.
Включение файла

Включение файла выполняется с помощью расширений со знаком доллара. Синтаксис имеет следующий вид:

$(include=filename )

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

Пример:
$(include=C:\Documents\MyScript.qvs);

Использование кавычек в скрипте

Существует несколько различных способов использования кавычек в операторах скриптов.

Внутри операторов LOAD

В операторе LOAD в качестве кавычек можно использовать указанные ниже символы:

Описание Символ Кодовая точка Пример

Имена полей

двойные кавычки

" " 34 "строка"
квадратные скобки [ ] 91, 93 [строка]
апострофы ` ` 96 `строка`

Строковые литералы

одиночные кавычки

' '

39 'строка'

В операторах SELECT

Для оператора SELECT, интерпретируемого драйвером ODBC, могут использоваться несколько иные кавычки. Обычно для названий полей и таблиц следует использовать прямые двойные кавычки (Alt + 0034), а для литералов — прямые одиночные кавычки (Alt + 0039) и не следует использовать апострофы. Однако в некоторых драйверах ODBC использование апострофов не только допускается, но и считается предпочтительным. В таком случае созданные операторы SELECT содержат апострофы в качестве символов кавычек.

Пример кавычек в Microsoft Access

Microsoft Access ODBC Driver 3.4 (входящий в Microsoft Access 7.0) во время анализа оператора SELECT принимает следующие кавычки:

Имена полей и имена таблиц:

[ ]

" " ` `

Строковые литералы:

' '

В других базах данных могут применяться другие обозначения.

Вне операторов LOAD

Снаружи оператора LOAD, там, где программа QlikView ожидает выражение, двойные кавычки обозначают ссылку на переменную, а не на поле. Если вы используете двойные кавычки, строка внутри кавычек будет интерпретироваться как переменная, значение которой будет использовано.

Ссылки на поле и ссылки на таблицы вне контекста

В некоторых функциях скрипта указываются ссылки на ранее созданные поля или на поля, которые являются результатом выполнения оператора LOAD, например Exists() и Peek(). Эти ссылки на поля называются внеконтекстными ссылками на поля, в отличие от ссылок на исходные поля, которые ссылаются на поля, находящиеся в контексте, то есть во входной таблице оператора LOAD.

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

Различия между именами и литералами

Разница между именами и литералами становится очевидной при сравнении следующих примеров:

Пример:
'Sweden' as Country

Когда данное выражение является частью списка полей в операторе LOAD или SELECT, текстовая строка «Sweden» будет загружена в качестве значения поля в поле QlikView «Country».

Пример:
"land" as Country

Когда данное выражение является частью списка полей в операторе LOAD или SELECT, содержимое поля базы данных или столбца таблицы с именем «land» будет загружено в качестве значения поля в поле QlikView «Country». Это означает, что land будет рассматриваться как ссылка на поле.

Различия между числами и строковыми литералами

Разница между числами и строковыми литералами становится очевидной при сравнении следующих примеров.

Пример:
'12/31/96'

Если данная строка используется как часть выражения, в первом шаге она будет интерпретироваться как текстовая строка «12/31/96», которая в свою очередь может интерпретироваться как дата, если выбран формат даты «MM/DD/YY». В этом случае она будет храниться как двойное значение с числовым и текстовым представлениями.

Пример:
12/31/96

Когда эта строка используется как часть выражения, она будет интерпретироваться как числовое выражение, то есть как 12, деленное на 31, деленное на 96.

Использование символа одинарной кавычки внутри строки

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

Пример:
Строка '10 O''clock News'> будет интерпретирована как «10 O'clock News».

Подстановочные знаки в данных

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

Символ звездочки

Символ звездочки интерпретируется как все (перечисленные) значения этого поля, т. е. значение, указанное в каком-либо другом месте в этом таблице. При использовании в одном из системных полей (USERID, PASSWORD, NTNAME или SERIAL) в таблице, загруженной в разделе доступа скрипта, интерпретируется как все (также и не перечисленные) возможные значения этого поля.

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

Символ звездочки недоступен, если явно не задан.

OtherSymbol

Во многих случаях требуется способ отображения всех других значений в таблице, т. е. всех значений, которые не были явно найдены в загруженных данных. Это выполняется с помощью специальной переменной с именем OtherSymbol. Чтобы обрабатывать OtherSymbol как «все другие данные», используйте следующий синтаксис:

SET OTHERSYMBOL=<sym>;

перед оператором LOAD или SELECT. Символ <sym> может быть любой строкой.

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

Для сброса этой функции используйте:

SET OTHERSYMBOL=;

Пример:

Table Customers
CustomerID Name
1 ABC Inc.
2 XYZ Inc.
3 ACME INC
+ Undefined

Table Orders
CustomerID Name
1 1234
3 1243
5 1248
7 1299

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

SET OTHERSYMBOL=+;

Любые ссылки на элемент CustomerID, кроме 1, 2 или 3, например, при нажатии элемента OrderID 1299, приведут к отображению значения Undefined для параметра Name.

Символ OtherSymbol не может использоваться для создания outer joins между таблицами.

Обработка значения NULL

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

В рамках логики QlikView как действительные значения NULL обрабатываются следующие значения:

  • Значения NULL, возвращаемые из соединения ODBC.

  • Значения NULL, созданные в результате принудительного объединения таблиц в скрипте загрузки.

  • Значения NULL, созданные в результате объединения в скрипте загрузки.

  • Значения NULL, созданные в результате создания комбинаций значений полей для отображения в таблице.

Эти значения NULL нельзя использовать для ассоциаций и выборок, кроме случаев использования оператора NullAsValue.

Текстовые файлы по определению не могут содержать значений NULL.

Связь/выбор значений NULL из ODBC

В источнике данных ODBC можно связать и/или выбрать значения NULL. Для этого определяется переменная скрипта. Может использоваться следующий синтаксис:

SET NULLDISPLAY=<sym>;

Символ <sym> заменит все значения NULL в источнике данных ODBC на самом низшем уровне ввода данных. Символ <sym> может быть любой строкой.

Для возврата к установкам интерпретации по умолчанию данной функции используйте следующий синтаксис:

SET NULLDISPLAY=;

При использовании NULLDISPLAY затронуты будут только данные из источника данных ODBC.

Если необходимо использовать логику программы QlikView для интерпретации значений NULL, возвращаемых из подключения ODBC в виде пустой строки, добавьте следующее выражение в скрипт перед оператором SELECT:

SET NULLDISPLAY=";

Здесь символ '' задается двумя отдельными апострофами без каких-либо промежуточных символов.

Создание значений NULL на основе текстовых файлов

Можно задать символ, который будет интерпретироваться в текстовом файле или в выражении inline как значение NULL. Используйте следующий оператор:

SET NULLINTERPRET=<sym>;

Символ <sym> интерпретируется как NULL. Символ <sym> может быть любой строкой.

Для возврата к установкам интерпретации по умолчанию данной функции задайте:

SET NULLINTERPRET=;
При использовании NULLINTERPRET будут затронуты только данные из текстовых файлов и выражений inline.

Передача значений NULL в выражения

Значения NULL передаются посредством выражения в соответствии с некоторыми логическими и весьма обоснованными правилами.

Функции

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

Пример:

asin(2)

возвращает

NULL

log(-5)

возвращает

NULL

round(A,0)

возвращает

NULL

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

Пример:

sin(NULL)

возвращает

NULL

chr(NULL)

возвращает

NULL

if(NULL, A, B)

возвращает

B

if(True, NULL, A)

возвращает

NULL

if(True, A, NULL)

возвращает

A

Исключением второго правила являются логические функции для проверки типа.

Пример:

isnull(NULL)

возвращает

True (-1)

isnum(NULL)

возвращает

False (0)

Арифметические и строковые операторы

Если значение NULL используется с любой стороны этих операторов, то возвращается значение NULL.

Пример:

A

+

NULL

возвращает

NULL

A

-

NULL

возвращает

NULL

A

/

NULL

возвращает

NULL

A

*

NULL

возвращает

NULL

NULL

/

A

возвращает

NULL

0

/

NULL

возвращает

NULL

0

*

NULL

возвращает

NULL

A

&

NULL

возвращает

A

Реляционные операторы

Если значение NULL используется с любой стороны реляционных операторов, то действуют особые правила.

Пример:

NULL

rel.op

NULL

возвращает

NULL

A

<>

NULL

возвращает

True (-1)

A

<

NULL

возвращает

False (0)

A

<=

NULL

возвращает

False (0)

A

=

NULL

возвращает

False (0)

A

>=

NULL

возвращает

False (0)

A

>

NULL

возвращает

False (0)

Файлы QVD

Файл QVD (файл данных QlikView) – это файл, в котором содержится таблица данных, экспортируемых из QlikView. QVD является собственным форматом QlikView и может быть создан только в QlikView и прочтен только с помощью QlikView. Формат файла оптимизирован для скорости при чтении данных из скрипта QlikView, но в то же время не занимает много места. Чтение данных из файла QVD обычно в 10–100 раз быстрее, чем чтение из других источников данных.

Файлы QVD можно читать в двух режимах: стандартном (быстром) и оптимизированном (сверхбыстром). Выбор режима производится обработчиком скриптов QlikView автоматически. Оптимизированный режим может использоваться только в том случае, если все загруженные поля считываются без трансформаций (формул, действующих в полях), но в то же время допускается изменение имен полей. Предложение WHERE в QlikView управляет распаковкой записей и отключает оптимизированную нагрузку. Во время трансформации поля оптимизированный файл .qvd извлекается и обрабатывается согласно стандартной функции ETL (извлечение, преобразование и загрузка), это приводит к ухудшению работы. Указанный формат не будет поддерживаться, вместо него будет использоваться и распознаваться базовый формат числа. Допускается выполнение трансформаций поля в файле .qvd. В результате бывший «оптимизированный» файл .qvd станет стандартным неоптимизированным файлом .qvd.

Файл QVD содержит только одну таблицу данных и состоит из трех частей:

  1. хорошо сформированного верхнего колонтитула XML (в кодировке UTF-8), который описывает поля в таблице, макета последующей информации и некоторых прочих метаданных;
  2. таблиц символов в формате, заполненном байтами;
  3. Фактические данные таблиц в формате, заполненном битами.

Цель файлов QVD

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

Увеличение скорости загрузки

Для больших объемов данных выполнение скрипта значительно увеличивается благодаря буферизации не изменяющихся или медленно изменяющихся блоков исходных данных в файлах QVD.

Снижение нагрузки на серверы базы данных

Объем данных, выбираемых из внешних источников данных, может также значительно сократиться. Это сокращает рабочую нагрузку на внешние базы данных и сетевой трафик. Более того, если несколько скриптов QlikView совместно используют одни и те же данные, необходимо лишь один раз загрузить их из базы данных источника в файл QVD. Другие приложения могут использовать те же данные с помощью файла QVD.

Объединение данных из нескольких приложений QlikView

При работе с оператором скрипта binary можно загрузить данные только из одного приложения QlikView в другое, но с файлами QVD скрипт QlikView может совместно использовать приложения QlikView. Это открывает возможности, например, для приложений по объединению похожих данных из разных подразделений и т.д.

Инкрементальная нагрузка

Часто функции QVD могут использоваться для облегчения инкрементальной нагрузки, т.е. эксклюзивной загрузки новых записей из постоянно растущей базы данных.

См.: Использование файлов QVD для инкрементальной нагрузки

Создание файлов QVD

Файл QVD можно создать одним из трех способов:

  1. Явное создание и присвоение имени с помощью команды сохранить в скрипте QlikView. Просто укажите в скрипте, что ранее считанную таблицу или ее часть необходимо экспортировать в явно названный файл в указанном вами местоположении.
  2. Автоматическое создание и обслуживание из скрипта. Путем постановки перед оператором load или select префикса buffer QlikView автоматически создает файл QVD, который в некоторых случаях может использоваться вместо оригинального источника данных при перезагрузке данных.
  3. Явное создание и присвоение имени вручную из макета с помощью Внутреннего преобразователя макросов. Данные можно экспортировать из макета QlikView с помощью команд графического интерфейса пользователя или макросов автоматизации. Формат QVD, помимо других возможных форматов экспорта, находится в команде графического интерфейса Экспорт…, которая находится в меню Объект большинства объектов листа.

    См.: Внутренний преобразователь макросов

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

Чтение Данных из Файлов QVD

QlikView может читать файл QVD или получать к нему доступ следующими методами:

  1. Загрузка файла QVD в качестве явного источника данных. Оператор load может ссылаться на файлы QVD в скрипте QlikView, как на любые другие типы текстовых файлов (csv, fix, dif, biff и т.д.). Мастер создания файла: Тип обращается с файлами QVD точно так же.

    См.: Мастер создания файлов: Тип

    Примеры:
    load * from xyz.qvd (qvd);
    load Name, RegNo from xyz.qvd (qvd);
    load Name as a, RegNo as b from xyz.qvd (qvd);
  2. Автоматическая загрузка буферизованных файлов QVD. При использовании префикса buffer с операторами load или select для чтения явные выражения не требуются. QlikView определяет степень, до которой эта программа использует данные из файла QVD, в отличие от получения данных с помощью оригинального оператора load или select.
  3. Доступ к файлам QVD с помощью скрипта. Для получения различной информации о данных, находящихся в верхнем колонтитуле XML файла QVD, может использоваться несколько функций скриптов (начинаются с qvd).

    См.: Функции файлов

Импорт файла QVD со звездочками не поддерживается.

Direct Discovery

Функция Direct Discovery в QlikView расширяет потенциальные варианты использования для Business Discovery, позволяя бизнес-пользователям выполнять ассоциативный анализ больших источников данных. Эта функция обеспечивает полный набор для ассоциативной работы программы QlikView в добавок к данным, поступающим непосредственно из больших источников данных, и позволяет совмещать большие данные и данные, хранящиеся в памяти. Direct Discovery позволяет использовать для анализа любые данные без ограничения масштабируемости.

Функция Direct Discovery объединяет возможности ассоциации наборов данных в памяти программы QlikView с моделью запроса, где не все исходные данные загружаются непосредственно в модель данных QlikView. Агрегированный результат выполнения запроса передается обратно в интерфейс пользователя, поэтому набор данных Direct Discovery является частью ассоциативной работы. Можно перемещаться между данными в памяти и данными Direct Discovery, как в едином наборе данных.

Можно создавать диаграммы для анализа данных из объединенных наборов данных, делать выборки из данных в памяти или данных Direct Discovery, а также просматривать ассоциации данных с одинаковыми цветами характеристик QlikView: зеленый, белый и серый.

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

Разница между Direct Discovery и данными в памяти

Модель в памяти

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

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

Direct Discovery

После загрузки полей таблицы с помощью оператора Direct Discovery LOAD (Direct Query) создается похожая таблица только с полями DIMENSION. Как и для полей в памяти, уникальные значения для полей DIMENSION загружаются в память. Но ассоциации между полями остаются в базе данных.

Значения поля MEASURE также остаются в базе данных.

Если определена структура Direct Discovery, поля Direct Discovery можно использовать вместе с определенными объектами диаграммы, а также их можно использовать для ассоциаций с полями в памяти. Если поле Direct Discovery используется, QlikView автоматически создает соответствующий запрос SQL для запуска во внешнем источнике данных. После выполнения выборок значения связанных данных полей Direct Discovery используются в условиях WHERE запросов базы данных.

При выполнении каждой выборки диаграммы с полями Direct Discovery пересчитываются с учетом вычислений, выполняемых в таблице исходной базы данных, с помощью запроса SQL, созданного QlikView. Можно использовать функцию условия вычисления, чтобы определить, когда диаграммы должны быть пересчитаны. Пока условие не будет выполнено, QlikView не отправляет запросы для пересчета диаграмм.

Разница в производительности между полями в памяти и полями Direct Discovery

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

Можно использовать рекомендации для стандартной базы данных и настройки запросов для Direct Discovery. Все действия по настройке производительности необходимо выполнять в исходной базе данных. Direct Discovery не поддерживает настройку производительности запросов из документа QlikView. Однако можно выполнять асинхронные параллельные вызовы базы данных с помощью функции объединения подключений. Синтаксис скрипта загрузки для настройки функции объединения:

SET DirectConnectionMax=10;

Кэширование QlikView также повышает удобство работы пользователей. См. приведенный ниже раздел Кэширование и Direct Discovery.

Производительность Direct Discovery с полями DIMENSION можно повысить с помощью отсоединения некоторых полей от ассоциаций. Для этого используется ключевое слово DETACH в DIRECT QUERY. Хотя отсоединенные поля не запрашиваются для ассоциаций, они по-прежнему являются частью фильтров, ускоряя время выборки.

Несмотря на то что данные полей в памяти QlikView и полей Direct Discovery DIMENSION хранятся в памяти, способ их загрузки влияет на скорость загрузки в память. Поля в памяти QlikView хранят только одну копию значения поля при наличии нескольких экземпляров одного значения. Однако загружаются все данные поля, а затем выполняется сортировка дубликатов.

Поля DIMENSION тоже хранят только одну копию значения поля, но сортировка повторяющихся значений выполняется в базе данных до загрузки в память. При работе с большими объемами данных, как обычно в Direct Discovery, данные загружаются намного быстрее с помощью DIRECT QUERY, чем это было бы с помощью загрузки SQL SELECT, используемой для полей в памяти.

Разница между данными в памяти и данными базы данных

DIRECT QUERY используется с учетом регистра при выполнении ассоциаций с данными в памяти. Direct Discovery выбирает данные из баз данных источника в соответствии с необходимостью учета регистра в базе данных. Если в базе данных регистр не учитывается, запрос Direct Discovery может возвращать данные, которые запрос в памяти не возвращает. Например, если следующие данные существуют в базе данных, в которой регистр не учитывается, запрос Direct Discovery значения "Red" вернет все четыре строки.

ColumnA ColumnB
red one
Red two
rED three
RED four

С другой стороны, выборка в памяти "Red," вернет только:

Red two

QlikView нормализует данные до такой степени, что выдает совпадения выбранных данных, которые база данных не находит. Таким образом, запрос в памяти возвращает больше совпадающих значений, чем запрос Direct Discovery. Например, в следующей таблице значения для числа "1" отличаются расположением пробелов вокруг них:

ColumnA ColumnB
' 1' space_before
'1' no_space
'1 ' space_after
'2' two

Если выбрать "1" на панели список для ColumnA с данными QlikView в стандартном формате в памяти, первые три строки будут связаны:

' 1' space_before
'1' no_space
'1 ' space_after

Если панель список содержит данные Direct Discovery, выборка "1" может связать только "no_space". Совпадения, найденные для данных Direct Discovery, зависят от базы данных. Некоторые возвращают только "no_space", а некоторые, как SQL Server, возвращают "no_space" и "space_after".

Кэширование и Direct Discovery

Кэширование QlikView сохраняет состояния выборки запросов в памяти. Поскольку выполняются одинаковые типы выборок, QlikView использует запрос из кэша вместо выполнения запроса исходных данных. Если выборки разные, запрос SQL отправляется в исходные данные. Кэшированные результаты совместно используются пользователями.

Пример:

  1. Пользователь применяет начальную выборку.

    SQL проходит по основному источнику данных.

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

    Возвращаются результаты из кэша, SQL не проходит по основному источнику данных.

  3. Пользователь применяет другую выборку.

    SQL проходит по основному источнику данных.

Можно установить ограничение времени для кэширования с помощью системной переменной DirectCacheSeconds. При достижении этого предела времени QlikView очищает кэш для результатов выполнения запросов Direct Discovery, созданных для предыдущих выборок. Затем QlikView запрашивает исходные данные для выборок и повторно создает кэш для указанного предела времени.

По умолчанию время кэширования для результатов выполнения запроса Direct Discovery составляет 30 минут, если не используется системная переменная DirectCacheSeconds.

Типы полей Direct Discovery

В Direct Discovery существует три типа полей данных: DIMENSION, MEASURE и DETAIL. Типы задаются согласно полям данных, если выполнена выборка Direct Discovery с помощью оператора Direct Query в скрипте загрузки.

См.: Direct Query

Все поля Direct Discovery можно использовать в сочетании с полями в памяти. Обычно поля с дискретными значениями, которые используются в качестве измерений, загружаются с ключевым словом DIMENSION, тогда как числа, используемые только при агрегировании, должны быть отмечены как поля MEASURE. Поля DIMENSION не могут использоваться в выражениях объекта.

В следующей таблице описаны характеристики и использование типов полей Direct Discovery.

Тип поля В памяти? Формирует ассоциацию? Используется в выражениях диаграммы?
DIMENSION Да Да Да
MEASURE Нет Нет Да
DETAIL Нет Нет Нет

Поля DIMENSION

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

Поля MEASURE

Поля MEASURE, с другой стороны, распознаются на «уровне метаданных». Поля MEASURE не загружаются в память (они не появляются в представлении таблицы). Цель — разрешить агрегирования данных в полях MEASURE, чтобы занять место в базе данных, а не в памяти. Тем не менее, поля MEASURE можно использовать в выражениях без изменения синтаксиса выражения. В результате конечный пользователь четко видит, как используются поля Direct Discovery из базы данных.

Следующие функции агрегирования можно использовать с полями MEASURE.

  • Sum — функция диаграммы
  • Avg — функция диаграммы
  • Count — функция диаграммы
  • Min — функция диаграммы
  • Max — функция диаграммы

Поля DETAIL

Поля DETAIL предоставляют информацию или подробные сведения, которые можно отобразить, но нельзя использовать в выражениях диаграммы. Поля DETAIL можно использовать только в функциях агрегирования Count и можно просматривать только в элементе Таблица. Поля, созданные как DETAIL, обычно содержат данные, которые нельзя агрегировать содержательным способом, таким как комментарии.

Любое поле можно обозначить, как поле DETAIL.

Источники данных, поддерживаемые в Direct Discovery

QlikView Direct Discovery может использоваться для следующих источников данных с 32-разрядными и 64-разрядными подключениями.

  • Источники данных ODBC/OLEDB — поддерживаются все источники ODBC/OLEDB, включая SQL Server, Teradata и Oracle.
  • Настраиваемые коннекторы с поддержкой SQL — коннектор SAP SQL, настраиваемые коннекторы QVX для хранилищ данных с поддержкой SQL.

Поддерживаются как 32-разрядные, так и 64-разрядные подключения.

SAP

Для SAP Direct Discovery можно использовать только с подключением Qlik SAP SQL Connector, для которого необходимо установить следующие параметры в переменных SET:

SET DirectFieldColumnDelimiter=' ';
SET DirectIdentifierQuoteChar=' ';

SAP использует OpenSQL, где в качестве разделителя столбцов используется пробел, а не запятая, поэтому операторы, установленные выше, могут выполнять замену для компенсации разницы между ANSI SQL и OpenSQL.

Google Big Query

Direct Discovery можно использовать с Google Big Query, для чего необходимо установить следующие параметры переменных set.

SET DirectDistinctSupport=false;
SET DirectIdentifierQuoteChar='[]';
SET DirectIdentifierQuoteStyle='big query'

Google Big Query не поддерживает SELECT DISTINCT или имена столбцов/таблиц, заключенные в кавычки, а также имеет конфигурацию цитирования не в кодировке ANSI, использующую '[ ]'.

MySQL и Microsoft Access

Direct Discovery можно использовать вместе с MySQL и Microsoft Access, но для этого может потребоваться установить следующие параметры переменных set для символов кавычек в этих источниках:

SET DirectIdentifierQuoteChar='``';

DB2, Oracle и PostgreSQL

Direct discovery можно использовать вместе с DB2, Oracle и PostgreSQL, но для этого может потребоваться установить следующие параметры переменных set для символов кавычек в этих источниках:

SET DirectIdentifierQuoteChar='""';

Sybase и MS SQL Server

Direct Discovery можно использовать вместе с Sybase и MS SQL Server, но для этого может потребоваться установить следующие параметры переменных set для символов кавычек в этих источниках:

SET DirectIdentifierQuoteChar='[]';

Ограничения при использовании Direct Discovery

Поддерживаемые типы данных

В Direct Discovery поддерживаются все типы данных, однако в некоторых случаях в QlikView необходимо определить отдельные форматы источников данных. Это можно сделать в скрипте загрузки, используя синтаксис "SET Direct…Format". В следующем примере показано, как определить формат данных исходной базы данных, используемой в качестве источника для Direct Discovery:

Пример:
SET DirectDateFormat='YYYY-MM-DD';

Имеется также две переменные скрипта для управления тем, как Direct Discovery форматирует значения денежных единиц в созданных операторах SQL:

SET DirectMoneyFormat (default '#.0000')
SET DirectMoneyDecimalSep (default '.')

Синтаксис для этих двух переменных такой же, как и для элементов MoneyFormat и MoneyDecimalSep, но есть два важных отличия в использовании:

  • Это не формат отображения, поэтому переменные не должны содержать символы валюты или разделители тысяч.
  • Значения по умолчанию не зависят от региона, но жестко связаны с этими значениями. (Формат, определенный для конкретного языка, включает в себя символ валюты.)

Direct Discovery поддерживает выбор расширенных данных Юникода путем использования стандартного формата SQL для строковых литералов расширенных символов (N’<расширенная строка>’), как это требуют некоторые базы данных (в частности SQL Server). Этот синтаксис можно включить для Direct Discovery с помощью переменной скрипта DirectUnicodeStrings. Если установить для этой переменной значение "true", то перед строковыми литералами будет использоваться "N".

Безопасность

При использовании Direct Discovery необходимо придерживаться следующих рекомендаций по безопасности:

  • Все пользователи, использующие одно приложение с функцией Direct Discovery, используют одно подключение. Сквозная проверка подлинности и отдельные учетные данные для каждого пользователя не поддерживаются.
  • Секция доступа поддерживается только в режиме сервера.
  • Можно выполнять пользовательские операторы SQL в базе данных с помощью ключевого выражения NATIVE так, чтобы для подключения к базе данных, настроенного в скрипте загрузки, использовалась учетная запись, для которой база данных доступна только для чтения.
  • В Direct Discovery нет функции ведения журнала, но можно использовать функцию трассировки ODBC.
  • Можно заполнять базу данных с помощью запросов от клиента.
  • Можно получить подробные сообщения об ошибках из файлов журнала сервера.

Не поддерживаемые функции QlikView

Вследствие интерактивной работы и характерного для SQL синтаксиса в Direct Discovery некоторые функции не поддерживаются:

  • Расширенные вычисления (анализ множества, сложные выражения)
  • вычисляемые измерения
  • Сравнительный анализ (другие состояния) в объектах, которые используют поля Direct Discovery
  • Функция поиска не поддерживает поля Direct Discovery MEASURE и DETAIL
  • Бинарная загрузка из приложения, которое обращается к таблице Direct Discovery
  • Цикл и сокращение (Loop and Reduce)
  • Синтетические ключи в таблице Direct Discovery
  • Присвоение имен таблицам в скрипте не применимо к таблице Direct Discovery
  • Использование подстановочного знака * после ключевого слова DIRECT QUERY в скрипте загрузки(DIRECT QUERY *)
  • Таблицы базы данных Oracle со столбцами данных типа LONG не поддерживаются.
  • Большие целые числа в научном представлении за пределами диапазона [-9007199254740990, 9007199254740991] могут приводить к ошибкам округления и неопределенному поведению.

Поддержка нескольких таблиц в Direct Discovery

Можно использовать Direct Discovery для загрузки нескольких таблиц или для просмотра с помощью функции объединения ANSI SQL. В одной диаграмме все меры должны быть из одной логической таблицы в QlikView, но она может состоять из нескольких таблиц из источника, связанных с помощью операторов join. Однако в одной диаграмме можно использовать измерения из разных таблиц.

Например, можно связать таблицы, загруженные в Direct Discovery с помощью предложения Where или Join.

  • Можно выполнить развертывание Direct Discovery в одной таблице фактов или многомерном сценарии в памяти с большими наборами данных.
  • Приложение Direct Discovery можно использовать с несколькими таблицами, которые соответствуют одному из следующих критериев.
    • Небольшое количество элементов ключевого поля в объединении.
    • Большое количество элементов ключевого поля в объединении, для параметра DirectEnableSubquery установлено значение «true» и все таблицы объединены с Direct Discovery.

      См.: Использование вложенных запросов с помощью Direct Discovery

  • Direct Discovery не подходит для развертывания в сценарии третьей нормальной формы со всеми таблицами в форме Direct Discovery.

Связывание таблиц Direct Discovery с помощью предложения Where

В этом примере скрипта данные загружаются из базы данных AW2012. Таблицы Product и ProductSubcategory связываются с помощью предложения Where, используя общее поле ProductSubCategoryID.

                Product_Join:
DIRECT QUERY
DIMENSION
         [ProductID],
         [AW2012].[Production].[Product].[Name] as [Product Name],
         [AW2012].[Production].[ProductSubcategory].[Name] as [Sub Category Name],
         Color,
         [AW2012].[Production].[Product].ProductSubcategoryID as [SubcategoryID]
MEASURE
         [ListPrice]
FROM [AW2012].[Production].[Product],
             [AW2012].[Production].[ProductSubcategory]
WHERE [AW2012].[Production].[Product].ProductSubcategoryID =
                [AW2012].[Production].[ProductSubcategory].ProductSubcategoryID ;

Связывание таблиц Direct Discovery с помощью предложений Join On

Также можно использовать предложения Join On для связи таблиц Direct Discovery. В этом примере оператора выполняется присоединение таблицы SalesOrderHeader к таблице SalesOrderDetail с помощью поля SalesOrderID, а также присоединение таблицы Customer к таблице SalesOrderHeader с помощью поля Customer ID.

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

Sales_Order_Header_Join:
DIRECT QUERY
DIMENSION
         AW2012.Sales.Customer.CustomerID as CustomerID,
         AW2012.Sales.SalesOrderHeader.SalesPersonID as SalesPersonID,
         AW2012.Sales.SalesOrderHeader.SalesOrderID as SalesOrderID,
         ProductID,
         AW2012.Sales.Customer.TerritoryID as TerritoryID,
         OrderDate,
         NATIVE('month([OrderDate])') as OrderMonth,
         NATIVE('year([OrderDate])') as OrderYear
MEASURE
         SubTotal,
         TaxAmt,
         TotalDue,
         OrderQty
DETAIL
         DueDate,
         ShipDate,
         CreditCardApprovalCode,
         PersonID,
         StoreID,
         AccountNumber,
         rowguid,
         ModifiedDate
FROM AW2012.Sales.SalesOrderDetail
         JOIN AW2012.Sales.SalesOrderHeader
         ON (AW2012.Sales.SalesOrderDetail.SalesOrderID =
                 AW2012.Sales.SalesOrderHeader.SalesOrderID)
         JOIN AW2012.Sales.Customer
         ON(AW2012.Sales.Customer.CustomerID =
                AW2012.Sales.SalesOrderHeader.CustomerID);

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

Журналирование доступа к данным с использованием Direct Discovery

Операторы Direct Discovery SQL, передаваемые в источник данных, можно записывать в файлы трассировки подключения базы данных. Для стандартного подключения ODBC трассировка запускается с помощью функции Администратор источника данных ODBC:

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

Direct Discovery с QlikView Server и Publisher

Параметры QlikView Server

Некоторые параметры QlikView Server следует проверить при использовании Direct Discovery в приложениях QlikView.

Эти параметры влияют на все документы QlikView в данном QlikView Server.

Object Calculation Time Limit

Поскольку функция Direct Discovery запрашивает внешнюю систему из QlikView, время вычисления диаграммы зависит от производительности базовой системы. Для параметра Object Calculation Time Limit на консоли управления QlikView Management Console следует задать значение, которое обеспечит наличие достаточного времени для того, чтобы диаграмма QlikView получила результаты выполнения запроса Direct Discovery от источника данных. Этот параметр находится на вкладке Performance в QlikView Server на консоли управления QlikView Management Console.

Макс. кол-во символов в диаграммах

Этот параметр позволяет задавать число точек диаграммы, отображаемых в диаграммах QlikView. Обратите внимание на то, что запрос Direct Discovery может вернуть много уникальных значений. Установите этот параметр так, чтобы приложение QlikView отображало необходимое число точек в диаграммах.
Параметр времени ожидания QVS в файле Config.xml

Когда функция Direct Discovery используется для запроса во внешнюю систему, отдельную от QlikView Server, следует настроить время ожидания сервера в файле config.xml, чтобы предоставить приложению QlikView достаточно времени на обратное получение результатов запроса. Недостаток времени на выполнение запросов при использовании клиента Ajax приводит к ошибке «Потеря подключения к серверу».

Значение по умолчанию равно 60 секундам. Может потребоваться увеличить значение параметра до максимального времени запроса. Файл config.xml находится в каталоге C:\ProgramData\QlikTech\WebServer. XML-тег — <QvsTimeout>. Обратите внимание, что при операциях обновления происходит перезапись параметра на значение по умолчанию.

QlikView Publisher

QlikView Publisher требует доступа на чтение к таблицам базы данных, используемым функцией Direct Discovery. Когда приложения QlikView, которые применяют Publisher, используют Direct Discovery, убедитесь, что учетная запись службы под управлением Publisher имеет требуемый доступ на чтение. Если это условие не выполняется, то служба Publisher не сможет прочитать таблицу Direct Discovery во время запланированных обновлений данных.

Лучшие способы моделирования данных

В настоящем разделе описывается ряд различных способов загрузки данных в документ QlikView в зависимости от структурирования данных и требуемой модели данных.

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

  1. Извлечение

    Первым шагом является извлечение данных из системы источника данных. Чтобы это определить, в скрипте используются операторы SELECT или LOAD. Различия между этими операторами заключаются в следующем:

    • SELECT используется для выбора данных из источника данных ODBC или поставщика OLE DB. Оператор SELECT SQL выполняется поставщиком данных, а не программой QlikView.
    • Оператор LOAD используется для загрузки данных из файла, из данных, определенных в скрипте, из ранее загруженной таблицы, с веб-страницы, из результата последующего оператора SELECT или путем автоматической генерации данных.
  2. Трансформация

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

    • Вычисление новых значений
    • Перевод закодированных значений
    • Переименование полей
    • Соединение таблиц
    • Агрегирование значений
    • Сведение
    • Проверка корректности данных
  3. Загрузка

    На последнем этапе скрипт запускается для загрузки определенной модели данных в документ.

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

  • центральная таблица фактов, содержащая ключи к измерениям и числа, используемые для вычисления мер (например, количество делений, объемы продаж и сметные суммы);
  • окружающие таблицы, содержащие измерения со всеми атрибутами (например, продукты, клиенты, категории, календарь и поставщики).
Во многих случаях можно решить задачу, например, для агрегирования, путем построения более емкой модели данных в скрипте загрузки или выполнения агрегирований в выражениях диаграмм. Как правило, производительность будет выше, если функции преобразования данных хранятся в скрипте загрузки.

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

Каждая таблица в модели данных обычно соответствует оператору SELECT или LOAD. Различия между этими операторами заключаются в следующем:

  • SELECT используется для выбора данных из источника данных ODBC или поставщика OLE DB. Оператор SELECT SQL выполняется поставщиком данных, а не программой QlikView.
  • Оператор LOAD используется для загрузки данных из файла, из данных, определенных в скрипте, из ранее загруженной таблицы, с веб-страницы, из результата последующего оператора SELECT или путем автоматической генерации данных.

Использование файлов QVD для инкрементальной нагрузки

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

Основной процесс приведен ниже:

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

2. Загрузите старые данные из файла QVD (загружается много записей, но процесс происходит значительно быстрее).

3. Создайте новый файл QVD.

4. Повторите процедуру для каждой загружаемой таблицы.

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

1) Случай 1: Только добавить (как правило, файлы протокола)

2) Случай 2: Только вставить (без обновления или удаления)

3) Случай 3: Вставить и обновить (без удаления)

4) Случай 4: Вставить, обновить и удалить

Ниже для каждого из этих решений находятся перечисленные решения. Чтение файлов QVD может происходить в «оптимизированном режиме» или «стандартном режиме». (Используемый метод выбирается обработчиком скриптов QlikView автоматически в зависимости от сложности действия). «Оптимизированный режим» быстрее «стандартного режима» приблизительно в 10 раз и в 100 раз быстрее загрузки базы данных обычным способом.

Случай 1: Только добавить

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

  • База данных должна являться файлом протокола (или другим файлом, в который записи добавляются, но не вставляются и не удаляются), который содержится в текстовом файле (без ODBC/OLE DB).

  • QlikView следит за числом записей, которые были прочитаны ранее, и загружает только те записи, которые были добавлены в конце файла.

Пример:
Buffer (Incremental) Load * From LogFile.txt (ansi, txt, delimiter is '\t', embedded labels);
Случай 2: Только вставить (без обновления или удаления)

Если данные находятся в базе данных, не являющейся простым файлом протокола, случай 1 не будет работать. Однако, проблему можно решить с минимальными дополнительными усилиями. Применяются следующие условия:

  • Источником данных может быть любая база данных.

  • QlikView загружает записи, вставленные в базу данных после выполнения последнего скрипта.

  • Поле ModificationDate (или похожее) требуется для распознавания новых записей программой QlikView.

Пример:
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD (qvd);
STORE QV_Table INTO File.QVD;

(Символы случайных значений в утверждении SQL WHERE определяют начало и конец даты. Правильный синтаксис даты в базе данных см. в руководстве по базе данных).

Случай 3: Вставить и обновить (без удаления)

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

  • Источником данных может быть любая база данных.

  • QlikView загружает записи, вставленные в базу данных или обновленные в базе данных после выполнения последнего скрипта

  • Поле ModificationDate (или похожее) требуется для распознавания новых записей программой QlikView.

  • Поле первичного ключа требуется для сортировки обновленных записей из файла QVD программой QlikView.

  • Это решение переведет чтение файла QVD в «стандартный режим» (а не в «оптимизированный режим»), что все равно значительно быстрее загрузки всей базы данных.

Пример:
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

Случай 4: Вставить, обновить и удалить

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

  • Источником данных может быть любая база данных.

  • QlikView загружает записи, вставленные в базу данных или обновленные в базе данных после выполнения последнего скрипта.

  • QlikView удаляет записи, удаленные из базы данных после выполнения последнего скрипта.

  • Поле ModificationDate (или похожее) требуется для распознавания новых записей программой QlikView.

  • Поле первичного ключа требуется для сортировки обновленных записей из файла QVD программой QlikView.

  • Это решение переведет чтение файла QVD в «стандартный режим» (а не в «оптимизированный режим»), что все равно значительно быстрее загрузки всей базы данных.

Пример:
Let ThisExecTime = Now( );

QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;
End If

Объединение таблиц с помощью операторов Join и Keep

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

Логика QlikView будет распознавать не отдельные таблицы, а результаты объединения, которые будут представлены в одной внутренней таблице. В некоторых случаях это требуется, однако существуют недостатки:

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

Функция Keep, которая позволяет уменьшить одну или обе таблицы до пересечения данных таблиц перед сохранением таблиц в программу QlikView, предназначена для уменьшения количества случаев, когда необходимо использовать явные объединения.

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

Join

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

Пример:
LOAD a, b, c from table1.csv;
join LOAD a, d from table2.csv;

Результирующая внутренняя таблица имеет поля a, b, c и d. Количество записей различается в зависимости от значений полей этих двух таблиц.

Имена объединяемых полей должны совпадать. Количество объединяемых полей может быть любым. Обычно в таблицах должно быть одно или несколько общих полей. При отсутствии общих полей будет рассматриваться декартово произведение таблиц. В принципе все поля могут быть общими, однако обычно в этом нет смысла. Пока имя ранее загруженной таблицы не будет указано в операторе Join, префиксом Join будет использоваться последняя созданная таблица. Поэтому порядок двух операторов не является произвольным.

Keep

Одной из основных функций программы QlikView является способность к связыванию таблиц вместо их объединения, что позволяет сократить использование памяти, повысить скорость обработки и гибкость. Функция keep предназначена для сокращения числа случаев необходимого использования явных объединений.

Префикс Keep между двумя операторами LOAD или SELECT приводит к уменьшению одной или обеих таблиц до пересечения их данных перед сохранением таблиц в программе QlikView. Перед префиксом Keep следует задать одно из ключевых слов: Inner, Left или Right. Выборка записей из таблицы осуществляется так же, как и при соответствующем объединении. Однако две таблицы не объединяются и сохраняются в программе QlikView в виде двух отдельных именованных таблиц.

Inner

Перед префиксами Join и Keep в языке скриптов QlikView можно использовать префикс Inner.

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

Если этот префикс используется перед Keep, он указывает, что две таблицы следует уменьшить до области взаимного пересечения, прежде чем они смогут быть сохранены в программе QlikView.

Пример:

В этих таблицах используются исходные таблицы Table1 и Table2:

Table1              Table2
A B
1 aa
2 cc
3 ee
      
A C
1 xx
4 yy
Примеры исходных таблиц Inner

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

VTable:
SELECT * from Table1;
inner join SELECT * from Table2;

VTable
A B C
1 aa xx
Пример Inner Join

Если вместо этого выполняется Inner Keep, таблиц все равно будет две. Две таблицы, разумеется, связаны посредством общего поля A.

VTab1:
SELECT * from Table1;
VTab2:
inner keep SELECT * from Table2;
VTab1              VTab2
A B
1 aa
      
A C
1 xx
Пример Inner Keep

Left

Перед префиксами Join и Keep в языке скриптов QlikView можно использовать префикс left.

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

Если этот префикс используется перед префиксом Keep, он указывает, что вторую таблицу следует уменьшить до области взаимного пересечения с первой таблицей перед сохранением в программе QlikView.

Пример:

В этих таблицах используются исходные таблицы Table1 и Table2:

Table1              Table2
A B
1 aa
2 cc
3 ee
      
A C
1 xx
4 yy
Примеры исходных таблиц Left

Сначала выполняется Left Join в отношении таблиц, в результате чего образуется таблица VTable, содержащая все строки из таблицы Table1, совмещенные с полями из совпадающих строк в таблице Table2.

VTable:
SELECT * from Table1;
left join SELECT * from Table2;

VTable
A B C
1 aa xx
2 cc -
3 ee -
Пример Left Join

Если вместо этого выполняется Left Keep, таблиц все равно будет две. Две таблицы, разумеется, связаны посредством общего поля A.

VTab1:
SELECT * from Table1;
VTab2:
left keep SELECT * from Table2;

VTab1              VTab2
A B
1 aa
2 cc
3 ee
      
A C
1 xx
Пример Left Keep

Right

Перед префиксами Join и Keep в языке скриптов QlikView можно использовать префикс right.

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

Если этот префикс используется перед префиксом Keep, он указывает, что первую таблицу следует уменьшить до области взаимного пересечения со второй таблицей перед сохранением в программе QlikView.

Пример:

В этих таблицах используются исходные таблицы Table1 и Table2:

Table1              Table2
A B
1 aa
2 cc
3 ee
      
A C
1 xx
4 yy
Примеры исходных таблиц Right

Сначала выполняется Right Join в отношении таблиц, в результате чего образуется таблица VTable, содержащая все строки из таблицы Table2, совмещенные с полями из совпадающих строк в таблице Table1.

VTable:
SELECT * from Table1;
right join SELECT * from Table2;

VTable
A B C
1 aa xx
4 - yy
Пример Right Join

Если вместо этого выполняется Right Keep, таблиц все равно будет две. Две таблицы, разумеется, связаны посредством общего поля A.

VTab1:
SELECT * from Table1;
VTab2:
right keep SELECT * from Table2;

VTab1              VTab2
A B
1 aa
      
A C
1 xx
4 yy
Пример Right Keep

Использование сопоставления как альтернативы соединению

Префикс Join в программе QlikView является мощным инструментом для объединения нескольких таблиц данных в модель данных. Единственным недостатком является то, что объединенные таблицы могут оказаться большими, из-за чего могут возникнуть проблемы при выполнении. Альтернативой соединению Join в ситуациях, когда необходимо найти одиночное значение в другой таблице, является сопоставление. В этом случае не будут загружены ненужные данные, которые могли бы замедлить вычисления и создать ошибки при вычислениях, поскольку при соединении может измениться число записей в таблицах.

Таблица сопоставления состоит из двух столбцов: поле сравнения (входные данные) и поле со значением сопоставления (выходные данные).

В этом примере у нас есть таблица с заказами (Orders). Нам необходимо узнать страну клиента, который указан в таблице клиентов (Customers).

Таблица данных Orders
OrderID OrderDate ShipperID Freight CustomerID
12987 2007–12–01 1 27 3
12988 2007–12–01 1 65 4
12989 2007–12–02 2 32 2
12990 2007–12–03 1 76 3

Таблица данных Customers
CustomerID Name Country
1 DataSales Spain
2 BusinessCorp Italy
3 TechCo Germany
4 Mobecho France

Чтобы найти страну (Country) клиента, нам нужна таблица сопоставления, которая выглядит следующим образом:

CustomerID Country
1 Spain
2 Italy
3 Germany
4 France

Таблица сопоставления, которую мы назвали MapCustomerIDtoCountry, определена в скрипте следующим образом:

   MapCustomerIDtoCountry:
   Mapping LOAD CustomerID, Country From Customers ;

Далее мы применим сопоставление с помощью функции ApplyMap при загрузке таблицы с заказами:

Orders:
   S *,
      ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country
      From Orders ;

Третий параметр функции ApplyMap используется, чтобы определить, какие значения вернуть. Если значение в таблице сопоставления не найдено, возвращается значение Null().

Результирующая таблица будет выглядеть так:

OrderID OrderDate ShipperID Freight CustomerID Country
12987 2007–12–01 1 27 3 Germany
12988 2007–12–01 1 65 4 France
12989 2007–12–02 2 32 2 Italy
12990 2007–12–03 1 76 3 Germany

Работа с перекрестными таблицами

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

Пример 1:

Year Jan Feb Mar Apr May Jun
2008 45 65 78 12 78 22
2009 11 23 22 22 45 85
2010 65 56 22 79 12 56
2011 45 24 32 78 55 15
2012 45 56 35 78 68 82

Если таблица просто загружается в программу QlikView, результат будет представлять собой одно поле для элемента Year и по одному полю для каждого месяца. Это, как правило, не то, что нужно. Вероятно, понадобится создать поля по трем месяцам — по одному для каждой категории заголовков (Year и Month) и одно для значений данных в матрице.

Для этого можно добавить префикс crosstable в операторы LOAD или SELECT, например:

crosstable (Month, Sales) LOAD * from ex1.xlsx;

При этом в программе QlikView будет получен следующий результат:

Year Month Sales
2008 Apr 12
2008 Feb 65
2008 Jan 45
2008 Jun 22
2008 Mar 78
2008 May 78
2009 Apr 22
2009 Feb 23
2009 Jan 11
2009 Jun 85
2009 Mar 22
2009 May 45

Перед перекрестной таблицей часто задается число столбцов классификации, которое может считываться прямым способом. В этом случае существует только один столбец классификации, Year:

Пример 2:

Salesman Year Jan Feb Mar Apr May Jun
A 2008 45 65 78 12 78 22
A 2009 11 23 22 22 45 85
A 2010 65 56 22 79 12 56
A 2011 45 24 32 78 55 15
A 2012 45 56 35 78 68 82
B 2008 57 77 90 24 90 34
B 2009 23 35 34 34 57 97
B 2010 77 68 34 91 24 68
B 2011 57 36 44 90 67 27
B 2012 57 68 47 90 80 94

В этом случае слева находятся два столбца классификации, после которых следуют столбцы матрицы. Число столбцов классификации можно задать в виде третьего параметра в префиксе crosstable следующим образом:

crosstable (Month, Sales, 2) LOAD * from ex2.xlsx;

При этом в программе QlikView будет получен следующий результат:

Salesman Year Month Sales
A 2008 Apr 12
A 2008 Feb 65
A 2008 Jan 45
A 2008 Jun 22
A 2008 Mar 78
A 2008 May 78
A 2009 Apr 22
A 2009 Feb 23
A 2009 Jan 11
A 2009 Jun 85
A 2009 Mar 22

Универсальные базы данных

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

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

GenericTable
object attribute value
ball color red
ball diameter 10 cm
ball weight 100 g
box color black
box height 16 cm
box length 20 cm
box weight 500 g
box width 10 cm

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

С другой стороны, смешанное отображение большого количества данных, таких как длина, цвет и толщина, тоже может быть неудобным.

Если эта база данных загружается в программу QlikView обычным способом и отображает данные в таблице, она выглядит следующим образом:

object
ball
box
   
attribute
color
diameter
height
length
weight
width
   
value
10 cm
16 cm
20 cm
100 g
500 g
black
red

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

object
ball
box
 
color
black
red
 
weight
100 g
500 g
 
length
20 cm


 


 


 

diameter
10 cm

 
width
10 cm

 
height
16 cm

 

Синтаксис достаточно прост:

Пример:
Generic SELECT* from GenericTable;

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

Сопоставление интервалов с дискретными данными

Префикс intervalmatch для операторов LOAD или SELECT используется для связывания дискретных числовых значений с одним или несколькими числовыми интервалами. Это очень полезная функция, которая может использоваться, например, в производственных средах, как показано в примере ниже.

Пример:

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

Table OrderLog
Start End Order
01:00 03:35 A
02:30 07:58 B
03:04 10:27 C
07:23 11:43 D

Table EventLog
Time Event Comment
00:00 0 Start of shift 1
01:18 1 Line stop
02:23 2 Line restart 50%
04:15 3 Line speed 100%
08:00 4 Start of shift 2
11:43 5 End of production

Сначала загрузите две таблицы как обычно, затем свяжите поле Time с интервалами, определенными полями Start и End:

SELECT * from OrderLog;
SELECT * from EventLog;
Intervalmatch (Time) SELECT Start,End from OrderLog;

Теперь в программе QlikView можно создать таблицу следующим образом:

Time Event Comment Order Start End
00:00 0 Start of shift 1 - - -
01:18 1 Line stop A 1:00 3:35
02:23 2 Line restart 50% A 1:00 3:35
04:15 3 Line speed 100% B 2:30 7:58
04:15 3 Line speed 100% C 3:04 10:27
08:00 4 Start of shift 2 C 3:04 10:27
08:00 4 Start of shift 2 D 7:23 11:43
11:43 5 End of production D 7:23 11:43

Отсюда видно, что в основном остановка линии повлияла на заказ A, а снижение скорости линии повлияло также на заказы B и C. Только заказы C и D были частично обработаны сменой Shift 2.

При использовании оператора intervalmatch обратите внимание на следующие моменты:

  • До оператора intervalmatch поле, которое содержит дискретные точки диаграммы (Time в приведенных выше примерах), уже должно быть считано в программу QlikView. Оператор intervalmatch сам не считывает это поле из таблицы базы данных!
  • Таблица, считанная с помощью оператора intervalmatch LOAD или SELECT, должна содержать ровно два поля (Start и End в примере, приведенном выше). Для установки связи с другими полями необходимо выполнить считывание из поля интервала, а также других дополнительных полей с помощью отдельного оператора LOAD или SELECT (первый оператор SELECT в указанном выше примере).
  • Интервалы всегда закрытые, т. е. конечные точки включены в интервал. Нечисловые пределы выводят игнорируемый интервал (неопределенный), а пределы со значением NULL расширяют интервалы до неопределенных значений (неограниченные интервалы).
  • Интервалы могут накладываться друг на друга, а дискретные значения будут связаны со всеми соответствующими интервалами.

Использование расширенного синтаксиса intervalmatch для разрешения неполадок с медленно изменяющимися измерениями

Расширенный синтаксис intervalmatch может использоваться для разрешения известных неполадок с медленно изменяющимися измерениями в исходных данных.

Пример скрипта:
SET NullInterpret='';

IntervalTable:
LOAD Key, ValidFrom, Team from IntervalTable.xls;
NullAsValue FirstDate,LastDate;

Key:
LOAD
Key,
ValidFrom as FirstDate,
date(if(Key=previous(Key),
previous(ValidFrom) — 1)) as LastDate,
Team
RESIDENT IntervalTable order by Key, ValidFrom desc;

drop table IntervalTable;

Transact:
LOAD Key, Name, Date, Sales from Transact.xls;

INNER JOIN intervalmatch (Date,Key) LOAD FirstDate, LastDate, Key RESIDENT Key;

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

Загрузка данных из оператора IntervalTable может привести к созданию следующей таблицы:

Key FirstDate Team
000110 Northwest
000110 2011–01–21 Southwest
000120 Northwest
000120 2013–01–06 Southwest
000120 2013–03–05 Northwest
000120 2013–03–05 Southwest

Оператор nullasvalue разрешает сопоставление значений NULL с полями в списке.

Создайте Key, FirstDate, LastDate (поля атрибутов) с помощью элементов previous и order by, после чего параметр IntervalTable исключается и заменяется этой таблицей ключей.

Загрузка данных из оператора Transact может привести к созданию следующей таблицы:

Key Name Date Sales
000110 Spengler Aaron 2009–08–18 100
000110 Spengler Aaron 2009–12–25 200
000110 Spengler Aaron 2011–02–03 300
000110 Spengler Aaron 2011–05–05 400
000120 Ballard John 2011–06–04 500
000120 Ballard John 2013–01–20 600
000120 Ballard John 2013–03–10 700
000120 Ballard John 2013–03–13 800
000120 Ballard John 2013–09–21 900

Последовательно заданные операторы intervalmatch и inner join заменяют вышеуказанный ключ синтетическим, подсоединяемым к таблице оператором Transact, что позволяет создать следующую таблицу:

Key Team Name FirstDate LastDate Date Sales
000110 Northwest Spengler Aaron 2011–01–20 2009–08–18 100
000110 Northwest Spengler Aaron 2011–01–20 2009–12–25 200
000110 Southwest Spengler Aaron 2011–01–21 2011–02–03 300
000110 Southwest Spengler Aaron 2011–01–21 2011–05–05 400
000120 Northwest Ballard John 2013–01–05 2011–06–04 500
000120 Southwest Ballard John 2013–01–06 2013–03–04 2013–01–20 600
000120 Southwest Ballard John 2013–03–05 2013–03–10 700
000120 Southwest Ballard John 2013–03–05 2013–03–13 800
000120 Southwest Ballard John 2013–03–05 2013–09–21 900

Создание интервала дат из одиночной даты

Иногда у интервалов времени нет четко выраженного начала и конца. Они подразумеваются только одним полем — меткой времени изменения.

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

Currency Change Date Rate
EUR 8.59
EUR 28/01/2013 8.69
EUR 15/02/2013 8.45
USD 6.50
USD 10/01/2013 6.56
USD 03/02/2013 6.30

Эта таблица определяет набор неперекрывающихся интервалов, где дата начала называется «Change Date», а дата конца определяется началом следующего интервала. Но поскольку дата конца хранится в своем столбце неявно, необходимо создать такой столбец, чтобы новая таблица стала списком интервалов.

Выполните следующие действия.

  1. Создайте файл с именем Rates.xlsx, содержащий таблицу, показанную выше, и сохраните его готовым к загрузке. Убедитесь, что даты в столбце Change Date указаны в локальном формате дат.
  2. Определите временной диапазон, с которым вы хотели бы работать. Начало диапазона должно предшествовать первой дате в данных, а конец диапазона должен быть после последней даты.
  3. Загрузите исходные данные, но измените пустые даты на начало диапазона, определенного в предыдущем маркере. Дата изменения должна быть загружена как «From Date».
  4. Отсортируйте таблицу сначала по элементу Currency, а затем по элементу «From Date» в убывающем порядке, так чтобы последние даты оказались сверху.
  5. Еще раз пройдитесь по данным, где вычисляется элемент «To Date». Если валюта текущей записи отличается от валюты предыдущей, то это первая запись новой валюты (но ее последнего интервала), поэтому следует использовать конец диапазона, определенного в шаге 1. Если это та же валюта, возьмите элемент «From Date» из предыдущей записи, вычтите небольшое количество времени и используйте это значение в текущей записи, как элемент «To Date».

Указанный ниже скрипт обновит исходную таблицу следующим образом:

Currency Rate FromDate ToDate
EUR 8.45 15/02/2013 vEndTime
EUR 8.69 28/01/2013 14/02/2013 23:59:59
EUR 8.59 vBeginTime 28/01/2013 23:59:59
USD 6.30 03/02/2013 vEndTime
USD 6.56 10/01/2013 02/02/2013 23:59:59
USD 6.50 vBeginTime 09/01/2013 23:59:59

Скрипт QlikView будет выглядеть так:
Let vBeginTime = Num('1/1/2013');
Let vEndTime = Num('1/3/2013');
Let vEpsilon = Pow(2,-27);
Tmp_Rates:
LOAD Currency, Rate,
            Date(If(IsNum([Change Date]), [Change Date], $(#vBeginTime))) as FromDate
From 'C:\MyFiles\Rates.xlsx'
(ooxml, embedded labels, table is Sheet1);

Rates:
LOAD Currency, Rate, FromDate,
            Date(If( Currency=Peek(Currency),
                        Peek(FromDate) — $(#vEpsilon),
                        $(#vEndTime)
                        )) as ToDate
            Resident Tmp_Rates
            Order By Currency, FromDate Desc;

Drop Table Tmp_Rates;

После запуска этого скрипта вы получите таблицу с правильным перечислением интервалов.

Впоследствии эту таблицу можно использовать при сравнении с существующей датой с помощью функции Intervalmatch.

Иерархии

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

NodeID ParentNodeID Title
1 - General manager
2 1 Region manager
3 2 Branch manager
4 3 Department manager

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

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

Пример:
Hierarchy (NodeID, ParentNodeID, Title, 'Manager') LOAD
   NodeID,
   ParentNodeID,
   Title
FROM 'hierarchy.txt' (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NodeID ParentNodeID Title Manager Title1 Title2 Title3 Title4
1 - General manager - General manager - - -
2 1 Region manager General manager General manager Region manager - -
3 2 Branch manager Region manager General manager Region manager Branch manager -
4 3 Department manager Branch manager General manager Region manager Branch manager Department manager

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

Таблица предков содержит одну запись для каждой связи дочерний-родительский элемент в данных. Она содержит ключи и имена дочерних и родительских узлов, т. е. в каждой записи указано, какому конкретному узлу принадлежит тот или иной узел. Ключевое слово hierarchybelongsto может использоваться в скрипте загрузки данных для преобразования таблицы смежных узлов в таблицу предков.

Семантические связи

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

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

Правила семантических таблиц

Семантические таблицы не отображаются в представлении таблицы.

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

  • Таблица должна содержать точно три или четыре столбца.
  • Семантическая таблица должна содержать взаимосвязи между значениями различных полей или одного поля. Сочетание и тех и других не допускается.
  • Оператор LOAD или SELECT, управляющий загрузкой семантической таблицы, должен следовать за классификатором semantic, означающим, что это не логическая таблица.

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

Если используется три столбца, явные имена для обратных связей не задаются. Имена, заданные во втором столбце, используются и для связей, и для обратных связей. До или после таких имен указаны стрелки.

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

Извлечение семантической таблицы из данных

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

В примере с президентами presidents в каталоге образцов QlikView скрипт создания связей для предшественников Predecessor и преемников Successor может выглядеть следующим образом:

Directory presidents;
LOAD * from presdnts.csv (ansi, txt, delimiter
is ',', embedded labels);
Semantic LOAD
No -1 as No,
'Successor' as Relation,
No,
'Predecessor' as Relation
from presdnts.csv (ansi, txt, delimiter is ',',
embedded labels) where No > 1;

Второй оператор LOAD создает таблицу, подобную указанной справа, которая загружается как семантическая таблица. Выражение where используется для пропуска первой записи, так как она может связать первое значение president со значением nonexistent 0:th president.

Обратите внимание на то, что этот оператор LOAD содержит два поля с метками No и два поля с метками Relation. Такой оператор LOAD может привести к возникновению ошибки при выполнении скрипта, если используется для загрузки внутренней таблицы, так как при загрузке единой внутренней таблицы все поля должны иметь разные имена. Невозможно использовать и соответствующий оператор SELECT, т. к. большинство драйверов ODBC также предъявляют подобные требования. Если таблица с президентами находится в базе данных, вместо этих операторов следует использовать следующую структуру:

Connect to DataBase;
SELECT * from presdnts;
Alias No2 as No, Relation2 as Relation;
Semantic SELECT
No -1 as No,
'Successor' as Relation,
No as No2,
'Predecessor' as Relation2
from presdnts where No > 1;

Пример с президентами — только один простой пример использования семантических связей. Эту структуру можно также использовать для установки генеалогических связей, в которых семантические связи могут быть следующими: двоюродный брат (сестра), брат (сестра), бабушка и т. п., или связей между сотрудниками компаний, для которых семантические связи могут быть следующими: superior, reports to, secretary и т. п.

Использование связанных значений в качестве имен связей

Иногда в качестве имен связей следует использовать связанные значения полей. Если рассматривать пример с президентами (presidents), то всех предшественников (predecessors) можно указать в одном столбце, а всех преемников (successors) в другом:

Для создания этих связей требуется следующий скрипт:

LOAD
No as DuplicateOfNo,
FirstName & ' ' & LastName as Name,
*
from presdnts.csv;
Semantic LOAD
No -1 as No,
FirstName & ' ' & LastName as Successor,
No as DuplicateOfNo,
'Dummy1'
from presdnts.csv where No > 1;
Semantic LOAD
No +1 as No,
FirstName &' ' & LastName as Predecessor,
No as DuplicateOfNo,
'Dummy2'
from presdnts.csv;

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

Возможно, с первого взгляда это и не очевидно, но обратные связи в вышеуказанной структуре практически бесполезны. Они должны бы показывать имя президента и, при щелчке, осуществлять выбор предшественника/преемника отображаемого президента. Поэтому такие связи называются Dummy1 и Dummy2, при этом используется только первая связь (столбец номер два).

Поскольку пустые связи в списках не нужны, второй и четвертый столбец должны обрабатываться как связи разных типов. Это значит, что первый и третий столбец должны иметь разные имена полей. По этой причине существует два столбца, содержащих номер президента, No и DuplicateOfNo.

Требуется два разных оператора semantic, чтобы создать два разных списка со связями.

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

Привязка информации к значениям поля

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

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

Оператор загрузки данной таблицы в виде информационной таблицы может выглядеть следующим образом:
Info LOAD Country, I from Flagsoecd.csv (ansi, txt, delimiter is ',', embedded labels);

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

Если во втором столбце введен текст, текст отображается во внутреннем обозревателе текста.

Чтобы отметить новую линию в этом тексте, нельзя использовать символ «перевод строки». Вместо этого в файле info используется символ "\n".

  • Если во втором столбце введено имя файла изображения (например, расширение bmp), изображение отображается во внутреннем обозревателе изображений.
  • Если во втором столбце введено имя звукового файла (расширение wav), воспроизводится звуковой сигнал.
  • Если во втором столбце введено имя исполняемого файла, выполняется этот файл.
  • Если во втором столбце введено имя любого другого файла, для его открытия используется соответствующая программа.
  • Если во втором столбце введен URL-адрес, например, интернет-адрес, для доступа к этому адресу используется зарегистрированный веб-браузер.

В файле info не может содержаться символ *. В то же время допускается использование символа OtherSymbol.

С помощью оператора bundle info load можно объединить в пакет внешние данные в документе QlikView.

Очистка данных

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

Таблицы сопоставления

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

Правила:
  • Таблица сопоставления должна состоять из двух столбцов, первый из которых содержит значения, используемые для сравнения, а второй — желаемые значения для сопоставления.
  • Двум столбцам следует присвоить имена, но имена сами по себе не важны. Имена столбцов не связаны с именами полей в обычных внутренних таблицах.

Использование таблиц сопоставления

При загрузке нескольких таблиц, содержащих список стран, можно обнаружить, что одна и та же страна имеет разные названия. В этом примере США указано как US, U.S. и United States.

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

Скрипт должен выглядеть следующим образом:
CountryMap:
Mapping LOAD x,y from MappingTable.txt
(ansi, txt, delimiter is ',', embedded
labels);
Map Country using CountryMap;
LOAD Country,City from CountryA.txt
(ansi, txt, delimiter is ',', embedded labels);
LOAD Country, City from CountryB.txt
(ansi, txt, delimiter is ',', embedded labels);

Оператор mapping загружает файл MappingTable.txt в качестве таблицы сопоставления с меткой CountryMap.

Оператор map разрешает сопоставление поля Country с помощью ранее загруженной таблицы сопоставления CountryMap.

Операторы LOAD загружают таблицы CountryA и CountryB. Эти таблицы, которые будут объединены вследствие того, что имеют одинаковый набор полей, включают поле Country, значения которого будут сравниваться со значениями в первом столбце таблицы сопоставления. Будут найдены значения полей US, U.S. и United States и заменены значениями из второго столбца таблицы сопоставления, т. е. USA.

Автоматическое сопоставление в цепочке событий, заканчивающейся сохранением поля в таблице программы QlikView, выполняется в последнюю очередь. Примерный порядок событий для стандартных операторов LOAD или SELECT показан ниже:

  1. Расчет выражений.
  2. Изменение имен полей с помощью оператора as.
  3. Изменение имен полей с помощью оператора alias.
  4. Квалификация имени таблицы, если применимо.
  5. Сопоставление данных, если совпадает имя поля.

Таким образом, сопоставление выполняется не при каждом появлении имени поля как части выражения, а тогда, когда значение сохраняется в таблице программы QlikView под определенным именем поля.

Чтобы отключить сопоставление, используйте оператор unmap.

Для сопоставления на уровне выражения используйте функцию applymap.

Для сопоставления на уровне подстроки используйте функцию mapsubstring.

Вызов функций VBScript из скрипта

Функции VBScript или JScript, заданные в макромодуле документа QlikView, могут быть вызваны из скрипта. Если вызываемая функция не распознается как стандартная функция скрипта, выполняется проверка наличия пользовательских функций в модуле. Это дает большую свободу с точки зрения определения пользовательских функций. Пользовательские макросы выполняются медленнее стандартных функций.

rem *************************************************
rem ***THIS IS VBSCRIPT CODE FROM THE MODULE********* rem**************************************************
rem ********* Global variables ********************
dim flag
rem *******functions accessible from script *********
rem ************ wrap for input box ******************
function VBin(prompt)
VBin=inputbox(prompt)
end function

rem ************ clear global flag ******************
function VBclearFlag()
flag=0
end function
rem ********* test if reference has passed ***********
function VBrelPos(Ref, Current)
if Ref=Current then
VBRelPos="Reference"
flag=1
elseif flag=0 then
VBRelPos="Before "&Ref&" in table"
else
VBRelPos="After "&Ref&" in table"
end if
end function
// **************************************************
// *************THIS IS THE SCRIPT *****************
// *************************************************

let MaxPop=VBin('Max population in millions :');
// Ask limit

let RefCountry=VBin('Reference country :');
// Ask ref.

let dummy=VBclearFlag(); // Clears the global flag

Load
Country,recno(),
Capital,
"Area(km.sq)",
"Population(mio)",
VBrelPos('$(RefCountry)',Country)
as RelativePos
from country1.csv
(ansi, txt, delimiter is ',', embedded labels)
where "Population(mio)" <= $(MaxPop);

Передача параметров

Следующие правила применяются к передаче параметров между скриптом загрузки и VBScript:

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

Оптимизация производительности приложений

Введение

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

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

Далее приведен список примеров прикладных методов для решения вышеуказанных проблем. Они позволят продемонстрировать проблему и показать полезные функции QlikView. Невозможно дать какие-то общие рекомендации относительного того, какой из методов является наилучшим, однако можно руководствоваться порядком, в котором перечислены примеры.

If ( Condition(Текст),…)

Операторы If, в которых выполняется сравнение текста, как правило, требуют значительных ресурсов. В качестве решений можно предложить сопоставить текст с числами, например с помощью функции autonumber (см. примеры в предыдущем разделе), и/или выполнить проверку в скрипте.

Проверка текстовых строк выполняется медленнее, чем проверка числовых значений.

Рассмотрим выражение:
If (Alfa= ‘ABC’, ‘ABC’, left (Alfa, 2))

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

Load
*,
If (Alfa = ‘ABC’, 1, 0) as Flag

resident table_1 ;

Выражение пример следующий вид:

If ( Flag = 1,’ABC’, left (Alfa, 2))

и проверка выполняется с использованием меньшего количества ресурсов.

Sum ( If (Условие, ‘Имя_поля’…))

Здесь агрегация не зависит от измерений таблицы и результат распределяется по измерениям таблицы. Чтобы решить эту проблему, можно либо выполнять проверку в скрипте, а агрегацию в таблице, либо выполнять всю операцию в скрипте. Для этого существует множество способов, например: сопоставление интервалов, group by, peek, if…then…else.

В данном примере используются два этапа, а именно проверка «условия» и агрегация результата.

Возьмем предыдущий пример и добавим агрегацию

Sum ( If (Alfa= ‘ABC’, Num*1.25 , Num) )

Load
*,
If (Alfa = ‘ABC’, 1, 0) as Flag
resident table_1 ;

Выражение имеет следующий вид:

Sum ( If ( Flag = 1, Num* 1.25 , Num ) )

Агрегацию также можно выполнять напрямую в скрипте следующим образом:

table_2:

Load
*,
If (Alfa = ‘ABC’, 1, 0) as Flag
resident table_1 ;

table_3:

Load
Alfa,
If ( Flag = 1, Num* 1.25 , Num ) as NewNum
resident table_2 ;

table_4:

Load
Alfa,
Sum( NewNum ) as SumNum
resident table_3
group by Alfa ;

Агрегация выполняется по Alfa, поскольку эта величина является измерением в проверке.

If ( Условие, Sum(‘Имя_поля’)..)

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

If ( Условие1, Sum(‘Имя_поля’), If (Условие2, Sum(‘Имя_поля’)……..

Логика конструкции If…then else… концептуально проста, однако часто ее бывает нелегко контролировать. Мы уже видели примеры с сотнями вложенными уровнями. Это требует значительных ресурсов памяти и процессора. «Условия» часто можно заменить путем их трансформирования. Типичным примером является агрегирование выражения количества*цена, где цена представляет собой переменную. Это можно выполнить путем «сопоставления расширенного интервала». Если должны выполняться два условия, например «A И B», то проверку можно заменить условием «C».

Пример:
sum((GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY)
Replaces
Sum(
If((GAC12_EFCT_DT<= GIV23_REJ_DT and
GAC12_EXPIRE_DT>GIV23_REJ_DT) and
(GAC15_EFCT_DT<= GIV23_REJ_DT and GAC15_EXPIRE_DT>GIV23_REJ_DT),
GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY,
Null()))
and
Sum(
If(GAC12_EFCT_DT<= GIV23_REJ_DT,
If(GAC12_EXPIRE_DT>GIV23_REJ_DT,
If(GAC15_EFCT_DT<= GIV23_REJ_DT,
If(GAC15_EXPIRE_DT>GIV23_REJ_DT,
(GAC12_STD_COST * GAC15_EXCHANGE_RATE) * GIV24_DISP_QTY,
Null())))))

на считывание полей GAC12_STD_COST и GAC15_EXCHANGE_RATE как медленно меняющихся измерений.

См.: Использование расширенного синтаксиса intervalmatch для разрешения неполадок с медленно изменяющимися измерениями

Сортировка текста

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

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

Сортировка ASCII Буквенно-цифровая сортировка
A1 A1
A10 A4
A11 A5
A30 A6
A4 A10
A5 A11
A6 A30

Динамические заголовки и текстовые объекты

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

С другой стороны, если вычисляется заголовок объекта, то это вычисление выполняется каждый раз, когда происходит какое-то изменение. Существует множество способов определения условий показа, условий вычислений и т. д. Эти проверки также будут всегда выполняться.

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

Функции времени, например Now() и Today(), будут вычисляться каждый раз, когда требуется повторное вычисление. Особенно функция Now() может требовать значительных ресурсов, поскольку она требует повторного вычисления в приложении каждую секунду.

Например:
If ( ReloadTime()+3>Now(), 'Old Data', 'New Data')

Здесь можно использовать следующий вариант

If ( ReloadTime()+3>Today(), 'Old Data', 'New Data')

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

Триггеры макрокоманд («при изменении»)

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

Проблемы защиты данных

В большинстве случаев при последующих обсуждениях предполагается наличие существующей системы управления базами данных (СУБД), а также использование приложения QlikView в качестве инструмента вывода. Однако аргументы по-прежнему остаются в силе, если исходные данные представлены в виде текстовых файлов.

Безопасность и целостность данных

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

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

  • Безопасность подразумевает слежение за тем, чтобы у пользователей были соответствующие разрешения на операции, которые они пытаются выполнить.
  • Целостность подразумевает слежение за тем, чтобы то, что пытаются сделать пользователи, было правильным.

В основном мы будем говорить о безопасности данных, поскольку средства для обеспечения целостности данных имеются в системе СУБД.

Право на изменение данных

Первым шагом в области обеспечения безопасности является предотвращение случайного удаления или изменения данных пользователями. Для многопользовательских систем это означает использование операционной системы и системы управления базами данных с соответствующей защитой. Примерами таких операционных систем для компьютеров являются Windows NT или Novell. Примерами систем управления базами данных являются ORACLE, SQL Server или Informix.

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

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

Для одного пользователя большинство проблем безопасности не существует. Поэтому часто достаточно регулярно выполнять резервное копирование файлов данных.

Право на просмотр данных

Последняя проблема в области безопасности данных связана с безопасностью при обращении с конфиденциальной информацией. Если приведенные выше проблемы безопасности были связаны с правом на изменение данных, то данная проблема связана с правом просмотра данных. Большинство систем управления базами данных содержат средства для предотвращения просмотра данных, находящихся в базе данных. Однако они не могут запретить просмотр копии данных, находящихся в файле QlikView. Поэтом приложение QlikView содержит свои собственные средства для предотвращения просмотра данными людьми, не имеющими соответствующих разрешений. Однако следует иметь в виду, что средство для ограничения доступа в приложении QlikView отслеживает только право на просмотр данных. QlikView не может предотвратить уничтожение данных пользователями с помощью других средств. Это может обеспечить только операционная система.

Целостность данных

Целостность данных подразумевает упорядоченный поток данных. Необходимо определить процедуры ввода данных и следить за тем, чтобы данные вводились одинаковым образом. Отличным решением этой проблемы является разработка форм в системе СУБД. Формы также предотвращают ввод пользователями в базу данных недопустимых значений, например несуществующих номеров заказчиков.

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

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

Подписка

RSS-материал