Есть случаи, когда можно кодить в разы быстрее, благодаря автоматизации. Сегодня хочу поделиться переводом интересной статьи Ричарда Пирса о способах автоматизации работы с календарями, когда у нас есть временные выражения для одинаковых временных разрезов. Например, у нас есть набор выражений с временными периодами, для каждого из которых нам нужно внести изменения. Изменения должны быть произведены в одинаковых временных отрезках (то есть, например, в выражениях по прошлой недели, прошлому месяцу, прошлому году, в сопоставлении данных и т.д.).
Сравнение временных отрезков: Немного теории
Давайте представим, что у вас есть выражение Sum(Sales), и вы хотите сделать сравнения по временным периодам:
- Последние продажи
- Прошлая неделя
- Месяц-день
- Год-к-году и т.д.
Итак, нам нужно будет создать, как минимум пять выражений для каждого временного отрезка, что потребует достаточно много дополнительной работы. Так, лучше работать через ссылки на выражение Sum(Sales). Если Sum(Sales) меняется, вам нужно обновить его лишь один раз, тогда все зависимые выражения изменятся.
НА ЗАМЕТКУ!
Основное выражение: Num(Sum({< [<AS OF TEXT>] %DataType={‘Sales’} >} «Net Sales»),’#,###.##’)
Выражение, которое ссылается на другое выражение: $(=only({1<%CC_Expression_LabelID={‘ProductMarginNet’}> } %CC_Expression_Expression)) , связанное выражение ‘ProductMarginNet’
Так, для 40 выражений с 10 временными периодами такое написание будет означать, что нам нужно изменить всего 400 выражений. Итак, для снижения вероятности ошибок в коде, я решил автоматизировать этот процесс. Когда у нас регулярно обновляются данные, мы увеличиваем вероятность ошибки при ручном вводе. Здесь же мы попросим QlikView выполнить работу самостоятельно.
Изменение во временных выражениях: Как автоматизировать
Все наши выражения у меня хранятся в рабочей книге Excel, у нас есть такие поля:
- %LabelID
- %CC_Expression
- %CC_ReplicateReferenceCalculations_AsOf
На втором листе ‘As Of’, который содержит мета-данные для сравнений дат разный типов, поля имеют вид:
- Префикс.
- Замена текста.
- Замена анализа множеств.
Процесс автоматизации работает следующим образом:
- Читаем данные по строкам в листе ‘As Of’ и мета-данные
- Загружаем все выражения, которые ссылаются на другие выражения, заменяем текст на анализ множеств.
- Создаем новое выражение с отличающимся ID.
- Затем последовательно читаем каждое выражение.
- Смотрим, сколько раз ссылается это выражение на другое.
- Затем меняем изначальный ID на новый.
Рассмотрим каждый этап подробно и разберем наиболее важные куски кода ниже:
- Читаем данные по строкам в листе ‘As Of’ и мета-данные
Мы исходим из того, что пользователь может самостоятельно определить меры и измерения визуализаций через выбор возможных опций из списка.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
AS_OF_Text_To_Replace: LOAD Prefix, [Text to Replace], [Replacement Set Analysis], [Additional Label], Selection, [Additional Help], [Fields to Keep] FROM [$(vQVD_ConfigDirectory)Custom Chart and Variables v0.1.xlsx] (ooxml, embedded labels, table is [As Of]) Where not isnull([Text to Replace]); LET vAsOf_TextToReplace = Peek(‘Text to Replace’,0,’AS_OF_Text_To_Replace’); LET vASOf_NumberOfReplacements = NoOfRows(‘AS_OF_Text_To_Replace’); FOR vASOf_NumberOfReplacementsCount = 0 to vASOf_NumberOfReplacements-1 // Идем по листу ‘As Of’ строка за строкой!! // Читаем основные данные книги LET vAsOf_Prefix = Peek(‘Prefix’,$(vASOf_NumberOfReplacementsCount),’AS_OF_Text_To_Replace’); LET vAsOf_ReplacementSetAnalysis = Peek(‘Replacement Set Analysis’,$(vASOf_NumberOfReplacementsCount),’AS_OF_Text_To_Replace’); LET vAsOf_AdditionalLabel = Peek(‘Additional Label’,$(vASOf_NumberOfReplacementsCount),’AS_OF_Text_To_Replace’); LET vAsOf_AdditionalHelp = Peek(‘Additional Help’,$(vASOf_NumberOfReplacementsCount),’AS_OF_Text_To_Replace’); LET vAsOf_Selection = Peek(‘Selection’,$(vASOf_NumberOfReplacementsCount),’AS_OF_Text_To_Replace’); |
2) Загружаем все выражения, которые ссылаются на другие выражения и заменяем текст на анализ множеств. 3) Создаем новое выражение с отличающимся ID
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// Первая загрузка прорабатывает все основные выражения. Это те выражение, которые никуда не ссылаются, такие как: sum({<%DataType={‘Sales’}>}%RecordCount) CustomChart_TempLoad2: LOAD Replace(%CC_Expression,’$(vAsOf_TextToReplace)’,Replace(‘$(vAsOf_ReplacementSetAnalysis)’,’^’,’$’)) AS %CC_Expression // Это строка кода найдет все сущности ‘[<AS OF TEXT>]’ и заменит их на новое значение ,%CC_HelpText & ‘ ‘ & ‘$(vAsOf_AdditionalHelp)’ AS %CC_HelpText ,RowNo() AS %CC_SortOrder ,%CC_Type ,%GroupMulti ,%GroupTreeView ,’0′ AS %IncludeInOnly ,%Label & ‘ ‘ & ‘$(vAsOf_AdditionalLabel)’ AS %Label ,’$(vAsOf_Prefix)’ & %LabelID AS %LabelID // Создание нового ID ,%LabelShort & ‘ ‘ & ‘$(vAsOf_AdditionalLabel)’ AS %LabelShort ,’$(vAsOf_Selection)’ AS %Selection Resident CustomChart_TempLoad Where %AsOfExpressionFlag=1 and %CC_Type=’Expression’; |
4) Затем последовательно читаем каждое выражение
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
// когда создано новое выражение ThisYear_ProductMarginNet нам нужно убедиться, что мы создали новые выражения с новым ID ^(=only({1<%CC_Expression_LabelID={‘ThisYear_ProductMarginNet’}> } %CC_Expression_Expression)) ReferencingExpressions_Temp: NoConcatenate LOAD %CC_Expression ,%CC_HelpText ,%CC_Type ,%GroupMulti ,%GroupTreeView ,%IncludeInOnly ,%Label ,%LabelID ,%LabelShort Resident CustomChart_TempLoad Where %CC_ReplicateReferenceCalculations_AsOf=1 and %CC_Type=’Expression’; // здесь мы используем простой флаг ‘1’ в данных CC_ReplicateReferenceCalculations_AsOf LET vNumberOfRows_ReferencingExpressions = NoOfRows(‘ReferencingExpressions_Temp’); IF Not Isnull(vAsOf_Prefix) Then // Выполнить только это, если нет такого типа выражения как Flag_IsThisDate if vNumberOfRows_ReferencingExpressions>0 Then // у нас должно быть хотя бы одно выражение, которое ссылается на другое! |
5) Смотрим, сколько раз ссылается это выражение на другое
|
1 2 3 4 5 6 |
For vReferencingExpressions_Count = 0 to vNumberOfRows_ReferencingExpressions-1 // Каждая строка в таблице ReferencingExpressions Table, проходит через каждого выражение сущности LET vLabelID = Peek(‘%LabelID’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vExpressionValue = Replace(Peek(‘%CC_Expression’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’),Chr(39),’¬’); LET vCountInstancesOfExpressionReferences = SubStringCount(‘$(vExpressionValue)’,’%CC_Expression_LabelID=’); // Считает сколько раз раз ‘%CC_Expression_LabelID=’ появляется в выражении |
6) Затем меняем изначальный ID на новый
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
FOR vExpressionReferences_ReplaceCount = 1 to vCountInstancesOfExpressionReferences // Замена ‘%CC_Expression_LabelID=’ на новые ссылки // Полная строка текста для замены LET vReplaceStart = Index(‘$(vExpressionValue)’,’%CC_Expression_LabelID=’,$(vExpressionReferences_ReplaceCount)); LET vReplaceLengh = Index(‘$(vExpressionValue)’,’}>’,$(vExpressionReferences_ReplaceCount))-$(vReplaceStart)+1; LET vTextToReplace = Mid(‘$(vExpressionValue)’,$(vReplaceStart),$(vReplaceLengh)); // Определение оригинального %LableID для замены LET vReplaceStartLabelID = Index(‘$(vExpressionValue)’,’={‘,$(vExpressionReferences_ReplaceCount))+2; LET vReplaceLenghLabelID = Index(‘$(vExpressionValue)’,’}>’,$(vExpressionReferences_ReplaceCount))-$(vReplaceStartLabelID)-1; LET vTextToReplaceLabelID = Replace(Mid(‘$(vExpressionValue)’,$(vReplaceStartLabelID),$(vReplaceLenghLabelID)),’¬’,”); If Left(vTextToReplaceLabelID,Len(vAsOf_Prefix))<>vAsOf_Prefix Then // Не заменять более одного раза. LET vTextToReplaceTo = ‘%CC_Expression_LabelID={‘&’$(vAsOf_Prefix)’ & ‘$(vTextToReplaceLabelID)’&’}’; LET vExpressionValue = Replace(‘$(vExpressionValue)’,’$(vTextToReplace)’,’$(vTextToReplaceTo)’); ENDIF NEXT // Обновить все LET vExpressionValue = Replace(‘$(vExpressionValue)’,’¬’,Chr(39)); // Взять значения из таблицы ReferencingExpressions_Temp LET vAsOf_CC_HelpText = Peek(‘%CC_HelpText’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vAsOf_CC_Type = Peek(‘%CC_Type’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vAsOf_GroupMulti = Peek(‘%GroupMulti’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vAsOf_GroupTreeView = Peek(‘%GroupTreeView’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vAsOf_IncludeInOnly = Peek(‘%IncludeInOnly’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vAsOf_Label = Peek(‘%Label’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); LET vAsOf_LabelShort = Peek(‘%LabelShort’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’); // Записать новую строку в таблицу Concatenate (CustomChart_TempLoad2) LOAD ‘$(vAsOf_Prefix)’ & ‘$(vLabelID)’ AS %LabelID ,’$(vExpressionValue)’ AS %CC_Expression ,’$(vAsOf_CC_HelpText)’ & ‘ ‘ & ‘$(vAsOf_AdditionalHelp)’ AS %CC_HelpText ,’$(vAsOf_CC_Type)’ AS %CC_Type ,’$(vAsOf_GroupMulti)’ AS %GroupMulti ,’$(vAsOf_GroupTreeView)’ AS %GroupTreeView ,’0′ AS %IncludeInOnly ,’$(vAsOf_Label)’& ‘ ‘ & ‘$(vAsOf_AdditionalLabel)’ AS %Label ,’$(vAsOf_LabelShort)’& ‘ ‘ & ‘$(vAsOf_AdditionalLabel)’ AS %LabelShort ,’$(vAsOf_Selection)’ AS %Selection AutoGenerate 1; // Все! |
Друзья, на этом сегодняшняя автоматизация завершена. Хороших вам разработок!
Временные выражения — материалы для скачивания
Василий Петренко
B3
Максим
Stepan
Павел Карасев
Свежие комментарии