7 Replies Latest reply: Dec 22, 2016 11:35 AM by Rafael Salero RSS

    Create a procedure

    Rafael Salero

      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.

        • Re: Create a procedure
          Gysbert Wassenaar

          Something like this:

           

          SET MyVariable = 100;

           

          MyTable:

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

          • Re: Create a procedure
            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.htm

             

            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

            • Re: Create a procedure
              Rafael Salero

              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

              • Re: Create a procedure
                Lech Miszkiewicz

                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



                  • Re: Create a procedure
                    Rafael Salero

                    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)

                      • Re: Create a procedure
                        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

                          • Re: Create a procedure
                            Rafael Salero

                            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