Trying to convert the following SQl case statement in Qlik

[#1]

Select  Distinct a.[Region],a.Priority,isnull(b.[Licensee Name], d.[applicant Name])as 'Lincensee Name', a.[Clinical Review Date], a.[Funded date],

  a.[Start Date],  [Asset Status],a.[Asset Number],

  case when b.[Licence Number] is null then d.[Lodged Date] else b.[Lodged Date] end " Lodged Date" , a.[Asset Group],a.[Purchase Order Amount]

   From Enable_Asset AS a FULL OUTER join Enable_Licence as b on (a.[Licence Number]=b.[Licence Number])

   FULL OUTER JOIN Enable_SR AS c on (c.[Licence Number]=a.[Licence Number])

      FULL OUTER JOIN Enable_Application AS d on (d.[Licence Number]=a.[Licence Number])

where a.[Start Date] >='01/07/2013' and [Asset Status]='Funded'and a.[Asset Group]NOT IN ('Consumables','Prosthetics')and  a.Priority IS NOT NULL

and a.[Asset Number]='2006070210'

GROUP by a.[Region],a.Priority,isnull(b.[Licensee Name], d.[applicant Name]),b.[Licence Number], a.[Clinical Review Date], a.[Funded date],d.[Lodged Date],

  a.[Start Date],  [Asset Status],a.[asset number],b.[Lodged Date], a.[Asset Group],a.[Purchase Order Amount],[applicant Name]

ORDER BY a.[Funded date]  DESC

[Gysbert_Wassenaar]

For starters, you can simply use that sql statement as it is. Qlikview will pass it directly to your source database for execution. You don't need to convert this to Qlikview load statements.

In Qlikview you'd do something like:

Temp:

SQL Select ...fields_from_table_a ... Where ... ;

Join (Temp)

SQL Select ...fields_from_table_b ... ;

Join (Temp)

SQL Select ...fields_from_table_d ...  ;

Result:

Noconcatenate

LOAD distinct * Resident Temp Order By [Funded date];

Drop Table Temp;

This requires three connections to your database and fetching all the records from tables b and d, And it makes Qlikview do all the expensive join calculations and the reordering. In my opinion this is better done by the database server.

PS: I don't understand why it includes a group by clause. I don't see any aggregation function used. The group by clause also contains more fields than are returned in the select. That also doesn't make sense to me. And I don't know why there's an outer join with table c. I don't see any fields from table c being used anywhere.

[#3]

Thanks for your update. We are new to this product and the training we had was all to do with loading the tables via qlikview.

The reason to have the table C is to ensure all service request associated with a is captured. The groupby does not need to be there as I was mucking around to ensure I capture some of the key variables.

I am not totally conversant with the product as we are still trying after training. I have made a connection to the database to ensure I load the tables to qlik if you states that I do not have to load it then it's a great way of going about but not sure how to. Does it still generate a Schema (Table) and can I still slice and dice the data via Qlik.

Can you elaborate how I can directly interrogate the script on the database. Cause if not just with the tables I am missing data element hence having the case statement added to lodged date.

Cheers for all your help and support.

Kind Regards

François

[Gysbert_Wassenaar]

In Qlikview a load statement will create a table. If you want to extract data from a database with an sql statement you can use the SELECT keyword followed by the rest of the sql statement. SQL SELECT ... is also allowed and does the same. It passes the entire sql statement to the database for execution and loads the data that's returned by the database in a table.