I come from an old school programing background which I think helps me write code that takes a process through step by step. This is known as ‘cursering’ through the data by reading each record of a table and making decisions on it.

A good friend was told at university, and subsequently told me, ‘each time you do this a puppy dies’. Not literally of course! What he was basically saying is that he was taught this type of programing was slow and you should find another solution.

Sometimes it’s the only way. Not only that, by being able to do this you open yourself up to many new opportunities!

Followers of my blog may know about my Calendar (it’s been doing the rounds on the Qlik Community for some time now). It flags previous periods and makes date set analysis very simple. I also use a technique called ‘Custom Charts’ which is brilliant, all my customers love having that on their dashboards.

However, the two don’t go hand in hand. For example you may have the expression Sum( Sales) and you wish to do date comparison across:

• Latest
• Last Week
• MTD
• Last MTD Comparative
• YTD,
• etc, etc

You now need at least five expressions, one for each date range! Then you may have the added complication of other expressions referencing Sum ( Sales). Again, this comes up a lot, rather than re-typing Sum( Sales) you reference the original expression, therefore if Sum( Sales) changes you only need to update it once and all the dependent expression will pick up the change.

A basic expression: Num(Sum({< [<AS OF TEXT>] %DataType={'Sales'} >} "Net Sales"),'#,###.##')

An Expression that references another: $(=only({1<%CC_Expression_LabelID={'ProductMarginNet'}> } %CC_Expression_Expression)) is referencing the expression 'ProductMarginNet'

So, 40 expressions with 10 possible date ranges would mean a total of 400 expression. I don’t know about you but I didn’t fancy typing all those out let alone maintain them afterwards. The chances of something going wrong is very real indeed.

Automation of this was essential and this is what Cursering through the data is perfect for.

The files I used can be found here: https://community.qlik.com/docs/DOC-16045

 

How its done

Firstly all my expressions are held in a workbook and I have several key fields

• %LabelID
• %CC_Expression
• %CC_ReplicateReferenceCalculations_AsOf     (This denotes whether an expression references another)

I also have a second sheet ‘As Of’ that hold meta data for the different date comparison types, the key fields are:

• Prefix
• Text to Replace
• Replacement Set Analysis

 

The process runs through like this:

1. I read though each row in the ‘As Of’ table and read the key Meta Data
2. I load all the expressions that’s don’t reference another expression (these are the simple ones) and replace the Text to Replace with the Replacement Set Analysis
3. I create a new expression with an new distinct ID
4. I then read through all the other expression one by one (the ones that do reference another expression)
5. I see how many times that expression references another
6. I then change the original reference ID with the new one that has been just created (Point 3)

It was quite a challenge. Points 4-6 was extremely complex to write but taking it step by step you can piece it together slowly.

Hopefully you found this interesting and useful. The key message here is we’ve automated what would have been an unmanageable situation. All those expressions manually typed would have been almost impossible to do, especially during development when things often change.

Here I’ve broken down what would be the manual step by step process and got QlikView doing the hard work for me. What’s even better is now its complete I’ll be able to use it again and again.

Here is the code step by step. Not all the code, only the important pieces:

1) I read though each row in the ‘As Of’ table and read the key Meta Data

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’); // This is ‘[<AS OF TEXT>]’ and has to be consitant throughout the workbook
LET vASOf_NumberOfReplacements = NoOfRows(‘AS_OF_Text_To_Replace’);

FOR vASOf_NumberOfReplacementsCount = 0 to vASOf_NumberOfReplacements-1 // We’re cursering through the ‘As Of’ tab of the workbook line by line!!

// Read the basic data from the workbook
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) I load all the expressions that’s don’t reference another expression (these are the simple ones) and replace the Text to Replace with the Replacement Set Analysis + 3) I create a new expression with an new distinct ID

// This first load handles all the basic expressions. That is those that don’t reference each other such as: sum({<%DataType={‘Sales’}>}%RecordCount)
CustomChart_TempLoad2:
LOAD
Replace(%CC_Expression,’$(vAsOf_TextToReplace)’,Replace(‘$(vAsOf_ReplacementSetAnalysis)’,’^’,’$’)) AS %CC_Expression // This will find all instances of ‘[<AS OF TEXT>]’ and replace them with the new value
,%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 // New Distict LabelID
,%LabelShort & ‘ ‘ & ‘$(vAsOf_AdditionalLabel)’ AS %LabelShort
,’$(vAsOf_Selection)’ AS %Selection

Resident CustomChart_TempLoad
Where %AsOfExpressionFlag=1 and %CC_Type=’Expression’;

 

4) I then read through all the other expression one by one (the ones that do reference another expression)

// Before the loop starts pick out all the expressions that reference others, for example: ^(=only({1<%CC_Expression_LabelID={‘ProductMarginNet’}> } %CC_Expression_Expression)) is referencing the expression ‘ProductMarginNet’
// so when we create a new expression ThisYear_ProductMarginNet we need to make sure we also create new expressions for those linking to it with the new 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’; // Here we use a simple ‘1’ flag in the data CC_ReplicateReferenceCalculations_AsOf

LET vNumberOfRows_ReferencingExpressions = NoOfRows(‘ReferencingExpressions_Temp’);

Note: This table load is done before the loop starts!

IF Not Isnull(vAsOf_Prefix) Then // Only do this if not a Flag_IsThisDate type expression

if vNumberOfRows_ReferencingExpressions>0 Then // we have to have at least one expression that references another!

 

5) I see how many times that expression references another

 For vReferencingExpressions_Count = 0 to vNumberOfRows_ReferencingExpressions-1 // Each Row in the ReferencingExpressions Table, curser through each expression instance

LET vLabelID = Peek(‘%LabelID’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’);

LET vExpressionValue = Replace(Peek(‘%CC_Expression’,$(vReferencingExpressions_Count),’ReferencingExpressions_Temp’),Chr(39),’¬’); // Replace all ‘ with ¬, these will be changed back later
LET vCountInstancesOfExpressionReferences = SubStringCount(‘$(vExpressionValue)’,’%CC_Expression_LabelID=’); // Counts how often ‘%CC_Expression_LabelID=’ appears in the expression

6)  I then change the original reference ID with the new one that has just been created (Point 3)
FOR vExpressionReferences_ReplaceCount = 1 to vCountInstancesOfExpressionReferences // Loop through each ‘%CC_Expression_LabelID=’ Instance and replace it with the new reference

// The Complete line of text to replace
LET vReplaceStart = Index(‘$(vExpressionValue)’,’%CC_Expression_LabelID=’,$(vExpressionReferences_ReplaceCount));
LET vReplaceLengh = Index(‘$(vExpressionValue)’,’}>’,$(vExpressionReferences_ReplaceCount))-$(vReplaceStart)+1;
LET vTextToReplace = Mid(‘$(vExpressionValue)’,$(vReplaceStart),$(vReplaceLengh));

 

// Identify the original %LableID to change
LET vReplaceStartLabelID = Index(‘$(vExpressionValue)’,’={‘,$(vExpressionReferences_ReplaceCount))+2;
LET vReplaceLenghLabelID = Index(‘$(vExpressionValue)’,’}>’,$(vExpressionReferences_ReplaceCount))-$(vReplaceStartLabelID)-1;
LET vTextToReplaceLabelID = Replace(Mid(‘$(vExpressionValue)’,$(vReplaceStartLabelID),$(vReplaceLenghLabelID)),’¬’,”); // Take out any ¬ chr at this point as not needed

 

If Left(vTextToReplaceLabelID,Len(vAsOf_Prefix))<>vAsOf_Prefix Then // Don’t replace it more than once. Sometimes an expression can reference the same metric. Replace will do them all in one go.

LET vTextToReplaceTo = ‘%CC_Expression_LabelID={‘&’$(vAsOf_Prefix)’ & ‘$(vTextToReplaceLabelID)’&’}’;
LET vExpressionValue = Replace(‘$(vExpressionValue)’,’$(vTextToReplace)’,’$(vTextToReplaceTo)’);
ENDIF

 

NEXT

 

// Reapply all the ‘ chariters
LET vExpressionValue = Replace(‘$(vExpressionValue)’,’¬’,Chr(39)); // Replace all ‘ with ^, these will be changed back later

 

// Grab the other values from the ReferencingExpressions_Temp Table
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’);

 

// Write the new line to the table
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;
// Phew !