Send charts from qlik load script

Hello everyone, today with a little hack of the original post by Steve Dark (https://www.quickintelligence.co.uk/send-data-from-qlik-load-script/ ).

Basically the small tweak of the original function is the code for “&”

sub Encode(vEncodeMe, vEncoded)
let vEncoded = 
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(vEncodeMe
, ':', '%3a')
, '/', '%2f')
, '?', '%3f')
, '=', '%3d')
, '\', '%5c')
, '@', '%40')
, ' ', '+')
, '&', '%26') //added
;
end sub

Then, some example data

DATA:
Load 
	rowno() as id, 
    *
Inline [
Month, Value
Jan, 1
Feb, 10
Mar, 7
Apr, 8
May, 4
Jun, 20
Jul, 10
];

And the little secret sauce is https://image-charts.com/ , an online chart generator with the option of “GET” parameters. If you read the documentation, you will see there are a lot of options, but to keep this example simple we are going to generate a bar chart.

STR_DIM:
Load 
concat(Month,'|',id) as STR_DIM,
concat(Value,',',id) as STR_EXP
resident DATA;

//The "id" field is for sorting the data in the string, due to my regional configuration "Jan" has no numerical value

Let vDIM = Peek('STR_DIM', 0, 'STR_DIM');
Let vEXP = Peek('STR_EXP', 0, 'STR_DIM');

//Variables to create the axis

Set vBASE_URL = 'https://image-charts.com/chart?';

Let vCHART_TYPE = 'cht=bvg'; 
//bvg : BarChart Vertically Grouped
//bhg : BarChart Horizontally Grouped

Set vCHART_SIZE = 'chs=700x300';

Set vCHART_AXIS = 'chxt=x,y'; //Axis to show

Let vCHART_VALUES = 'chd=t:' & vEXP;

Let vCHART_LABELS = 'chxl=0:|' & vDIM; //Format <axis>:|<label1>|<label2>


Set vCHART_DATASERIES_LABEL = 'chdl=Visitors';

Let vCHART_URL = 
vBASE_URL & '&' & 
vCHART_TYPE & '&' & 
vCHART_SIZE & '&' & 
vCHART_LABELS & '&' &
vCHART_AXIS & '&' &
vCHART_VALUES & '&' &
vCHART_DATASERIES_LABEL
;

Then, the final step is embed the image in the HTML Body, to do this use the function Encode()

call Encode('<img src="$(vCHART_URL)" />', vMessage);

Also you can test the string in one of my favorites object  from Vizlib “Advance Text Object”, drawing the variable  vCHART_URL like this example

= '<img src="$(vCHART_URL)" />'

Result:

By the way, if you want to get rid of the watermark see the pricing!

Hope this little hack will help you.

See ya!

 

Split lines into words – Sympathy for the devil

If you saw the movie Focus maybe you remember this moment at the stadium musicalized with this song and this line of the dialog

The Mandarin word for “five” is woo. There are 124 “woo-woos” in “Sympathy for the Devil.”

Next a little clip with the full explanation about the scam…

So, is this info accurate? let’s find out…

First, get the lyrics of “sympathy for the devil lyrics” (just google “sympathy for the devil lyrics” and copy the result into a text file), then load the file into QlikSense.

[Lyrics]:
LOAD
    rowno() as idline,
    [@1:n] as line
FROM [lib://Desktop/Sympathy For The Devil.txt]
(fix, codepage is 28591, no labels);

The result as follows

Then, how to split the “line” field into single words? The subfield function to the rescue!

Load
    idline,
    rowno() as idword,
    SubField(line,' ') as word
resident Lyrics;

without the 3rd parameter, the function creates a new row for every splitted value, creating this structure

Now, the count….

Why “who” is different from “who,”? Cleaning up the values…

Load
    idline,
    rowno() as idword,
    keepchar(upper(SubField(line,' ')),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as word
resident Lyrics;

The final result as follows..

So, if we take the count for “WOO” and “WHO” give us 83.. a little far from the 124 expected.

Happy Qliking!

Customizing Qlik Sense script editor

Hi guys and girls!, in my first (useful I guess) post I will show you how to customize the script editor in QlikSense (Desktop or Server). This is a safe hack cause you are not modifying any core css file or using any extra js code.

First, install Stylish, this is a Chrome extension that enable to change the default css from known sites as Facebook, Twitter and others (go to https://userstyles.org/ and look around…). But you can also define your own style!!!, in this case for the script editor we will define some simple rules (not all of them) as follows…

Create a new style

And in the code section, write these styles

.CodeMirror pre {
background: black;
}
.cm-operator {
color: white;
}
.CodeMirror-scroll {
background: black;
}

Make sure to complete the textbox with URL begins with “http://localhost:4848/dataloadeditor/app/”

Also you can replace localhost, with the ip or server name for Qlik sense server

Then you can apply from the stylish menu

The result as follows

TODO:

  • Change other CSS elements/classes to complement the style
  • Maybe create an GitHub repository with script editor themes?

Happy scripting!

Hello world!

Welcome dear friend!

The main idea behind this blog will be to post useful info about topics like BI (particularly Qlik products), UX and things related.

Any feedback will be appreciated.

Let’s rock!