How to perform below sql query in qlikview ?

[#1]

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.

[hic]

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

[#3]

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.

[#4]

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.

[hic]

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

[#6]

Dear Henric,

We have over 40 tables in our data model and the two tables mentioned above are just a subset of the whole.

The calculations in SQL query pertain to developing a KPI required by the client, which is why the query or the calculation may not be as dynamic as otherwise.

The requirment is to get a PieChart that shows two segments - one for delayed Items (calculated using the SQL query we posted above) and the second will represent remaining (On Time Items).

Is there any way we could do this -- preferrably on the front end?

Thanks,

-Mukram.

[hic]

40 tables in a QlikView app is not uncommon. So I would recommend that you use that approach.

A pie chart with two segments can easily be made, e.g. using two expressions (and no dimension):

count(distinct if(ItemTransactionID = Max(total ItemTransactionID), ItemCode))

count(distinct if(ItemTransactionID < Max(total ItemTransactionID), ItemCode))

HIC

[#8]

Dear Henric.

Please refer this link.

http://community.qlik.com/thread/58950?start=0&tstart=0

for delayed Items expression like:

=Count (distinct(If(Timestamp(Max_CreationDate+(HangInPeriod)/24)< DateCompare,ItemCode1)))

where DateCompare is a variable. (Currently DateCompare contain a date for validation purpose only and may be later changed to reloadtime() in actual application.

Thanks,

Mukram.