7 Replies Latest reply: Aug 6, 2012 6:15 AM by qva_mukram RSS

    How to perform below sql query in qlikview ?

      Dear Community.

       

      hwo to perform below sql query in qlikview

       

       

      select count(Distinct V_I2.ItemCode) from [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS]AS V_I1

                                                  inner join (select [ItemCode],MAX(ItemTransactionID)AS MAX_T

                                                  from [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS]

                                                  GROUP by [ItemCode]) V ON V.ItemCode=V_I1.ItemCode

                                                 Inner join [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS] as V_I2 ON V_I2.ItemTransactionID=V.MAX_T

       

       

      Thanks,

      Mukram.

        • Re: How to perform below sql query in qlikview ?
          Henric Cronström

          If I understand the SQL correctly, you want to count the distinct ItemCode:s for which the ItemTransactionID:s equal the largest possible ItemTransactionID?

           

          Although you can use such a complex Select statement in the QlikView script, you shouldn't. Instead you should solve this problem in a much simpler way. First, load the transaction table. The script should be something like

           

          SQL Select

          ItemCode,

          ItemTransactionID,

          <other fields...>

          from [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS];

           

          Then you display the fields as list boxes. Further, you create a pivot table with ItemTransactionID as dimension and count(Distinct ItemCode) as expression. Sort the dimension numerically, descending.

           

          Now the value you want will be on the top row of the table. Or click on the largest ItemTransactionID, and you will see the relevant data.

           

          Bottom line: Your job as a QlikView developer is to create a data model and some objects that display information in a relevant way. But you should not make the selection for the user.

           

          HIC

            • Re: How to perform below sql query in qlikview ?

              Dear,

              Henric.

               

              Thanks for your reply.

               

              I am posting the entire query here. It might give  you a better idea.

              Also, I'm trying to put the entire calculations on the back end, so could you please suggest something for that?y:

               

               

               

              Query:

               

                     select count(Distinct V_I2.ItemCode)

                   from [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS]AS V_I1 inner join

                    (select [ItemCode],MAX(ItemTransactionID)AS MAX_T

                       from [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS]

                         where ItemTransactions_CreationDate >='2012-08-01 00:00:00' and ItemTransactions_CreationDate             <'2012-08-02 00:00:00'

                and  PostServiceID=5 and TransDirectorateFrom=20300

                GROUP by [ItemCode]) V ON V.ItemCode=V_I1.ItemCode

                Inner join [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS] as V_I2 ON V_I2.ItemTransactionID=V.MAX_T

                inner join [PostalSysDB].[dbo].[ServiceProcedures] AS SP ON V_I2.ProcedureID=SP.ProcedureID AND SP.ServiceID=5

                WHERE

                SP.HangInPeriod<>0 and DateAdd(hour,SP.HangInPeriod,V_I2.ItemTransactions_CreationDate)<'2012-08-02 12:00:00'

               

               

              Do i need to provide some more information?

               

               

              Thanks,

              Mukram.

                • Re: How to perform below sql query in qlikview ?
                  Henric Cronström

                  Putting all calculations in the back end is, as I see it, the wrong approach.

                  The idea with QlikView is that you should load a large set of data and let the user make the selections (set the filters). Thus, the calculations/aggregations must be made in real-time - when the user makes the selection. If you pre-aggregate everything, you also prevent the user from making the analysis.

                   

                  I think that your script should be something like

                   

                  SQL SELECT * FROM [PostalSysDB].[dbo].[V_ITEMS_TRANSACTIONS];

                  SQL SELECT * FROM [PostalSysDB].[dbo].[ServiceProcedures];

                   

                  ... possibly with the same where clauses as in your SQL query to limit the data slightly. Hence, two data tables in the QlikView data model. It seems as if ProcedureID is the only key and that it is named correctly in both tables, so it should work directly.

                   

                  HIC

                • Re: How to perform below sql query in qlikview ?

                  Dear Henric,

                   

                  Let me add more details here.

                   

                  In our data model, we have ItemTransactions and ServiceProcedures tables connected via ProcedureID field.

                  I'm trying to duplicate the result of the SQL query in QlikView where it can be represented using a Pie Chart, if possible.

                   

                   

                  Also, we are not making selections for the user. The filters (ItemTransactions_CreationDate,TransDirectorateFrom,PostServiceID and ServiceID) used in the query are a part of the requirement validation.

                   

                  Please let me know if a sample application is needed for more clarity.

                   

                  Thanks,

                   

                  -Mukram.