Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Create a procedure

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.

Tags (2)
1 Solution

Accepted Solutions
Lech_Miszkiewicz
Honored Contributor III

Re: Create a procedure

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



cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
7 Replies
MVP & Luminary
MVP & Luminary

Re: Create a procedure

Something like this:

SET MyVariable = 100;

MyTable:

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


talk is cheap, supply exceeds demand
Lech_Miszkiewicz
Honored Contributor III

Re: Create a procedure

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

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
Not applicable

Re: Create a procedure

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
Honored Contributor III

Re: Create a procedure

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



cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
Not applicable

Re: Create a procedure

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:

    

idticket_state_idcreate_timechange_time
12941202/01/201402/01/2014
12942202/01/201402/01/2014
12943202/01/201402/01/2014
12944202/01/201402/01/2014
12945202/01/201402/01/2014
12946202/01/201402/01/2014
12947202/01/201402/01/2014
12948202/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
Honored Contributor III

Re: Create a procedure

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

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
Not applicable

Re: Create a procedure

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