Create a procedure

[#1]

Hello,

I need rearrange data before loading it into Qlik sense.

Its possible to create a procedure in the sense application? If yes, whats the syntax?

something simple, just to understand

EX:     declare a variable

          and put the result of a select (SELECT last_year FROM otrs.ticket WHERE id = 100)

          and then read it into to Qlik sense

I don´t know is its important but i have a connection ODBC to a MYSQL database where I only have read permissions, therefore I cannot create it there.

[Gysbert_Wassenaar]

Something like this:

SET MyVariable = 100;

MyTable:

SQL SELECT last_year FROM otrs.ticket WHERE id = $(vMyVariable);

[Lech_Miszkiewicz]

Hi Rafael,

Executing procedures is possible in Qlik script. One option is to execute stored procedures on DB. You will use EXEC or SQL EXEC syntax to do so. Read this to get more understanding of syntax and requirements: https://help.qlik.com/en-US/sense/3.1/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/SQL.h...

If it is ODBC to MYSQL then as long as the driver supports SQL EXECUTE statement you should be OK.

The other thing is that in the past to run procedure like this on QlikView script you would need read&write access. I am not sure whether this is required in QlikSense. That may require some testing.

On the other hand if it is just simple "where" statement you do not have to run procedure and can get away with simple SQL Where statement which will work with simple read only access and you are able also to pass variable value into it. Syntax is like pure SQL.

hth

regards

Lech

[#4]

My objective here is to load data from a table and then create new columns, I believe need to be done in a procedure. The problem is that I cannot create one on my database because of my permissions.

I have a table with columns:

SELECT id, date_terminated

  FROM otrs.ticket;

What I want is to create a new table with the columns stated before and Jan_2014, Fev_2014, Mar_2014,..., Dez_2016 and then fill it with 1 or 0, for each line, if it was terminated on the last day of the month

So i want to do all of these in the Qlik Sense data load editor

[Lech_Miszkiewicz · Accepted solution]

Hi Rafael,

You dont need a procedure to achieve what you are trying to. It can be simply done in Qlik Load script. I assumed your script above is your starting point. To be honest you can do everything in one single step : see script below-->

FinalTable:

LOAD

     id,

     date_terminated

     If(Date(date_terminated) = Date(MonthEnd(MakeDate(2014,1,1))),1,0) as Jan_2014,

     If(Date(date_terminated) = Date(MonthEnd(MakeDate(2014,2,1))),1,0) as Feb_2014,

     If(Date(date_terminated) = Date(MonthEnd(MakeDate(2014,3,1))),1,0) as Mar_2014,

     If(Date(date_terminated) = Date(MonthEnd(MakeDate(2014,4,1))),1,0) as Apr_2014,

     ....

     ....       

     ....

     ....

;

SELECT

     id,

     date_terminated

FROM

     otrs.ticket

;

In your script you would have to create as many lines with this statement as you need up till 2016, just by changing this bit of script: MakeDate(2014,1,1))) as Jan_2014 in statement above

That will generate extra columns with those flags and i assume that will produce what you have asked for.

However I do not see benefit of having data structured this way as it becomes hard to maintain ( number of columns will increase and column names will change ). If you would describe your business case where you want to use those flags maybe we could find a better solution.

regards

Lech

[#6]

As you say its very difficult to grow, and the bar graph only supports a few measures (its columns will became a measure)

The data that I have is something like this:

id	ticket_state_id	create_time	change_time
12941	2	02/01/2014	02/01/2014
12942	2	02/01/2014	02/01/2014
12943	2	02/01/2014	02/01/2014
12944	2	02/01/2014	02/01/2014
12945	2	02/01/2014	02/01/2014
12946	2	02/01/2014	02/01/2014
12947	2	02/01/2014	02/01/2014
12948	2	02/01/2014	02/01/2014

And this id links to a another table (tickets) where i can find out what is the client that open the ticket and other stuff.

I also have a master calendar that i use in my visualizations, everything is linked to table tickets through id.

I want to create a line chart where i can see how my tickets were open at the end of the month. Where in the dimension is the month that exist in my master calendar (Jan - 2014 to Dez - 2016, at the moment)

[Lech_Miszkiewicz]

Hi,

If you want to create a chart (line chart ) where you wanted to count for each month number of opened tickets in last day of each of the month our solution from previous topics may not be necessary. I would assume that your calendar is linked to "create time" date. Here is what i would do:

• Create a line chart
• Add Dimesnion Month/Year
• Add Expression
  • count(distinct  {<create_time = {'$(=MonthEnd(create_time))'}>}  id)

see if it works.

I could help you bit more if you could provide a screenshot of your datamodel preview - so i could understand how is it created.

regards

Lech

[#8]

Thanks for helping me,

But I think that this expression will do is count the tickets that were open each month;

And what i need is the tickets that were open in each month. Something that will do the something like this expression: only count if create_time <= last_day_month and change_time > last_day_month