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
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.
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.
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.