topic Re: SQL conditions in Qlikview in QlikView

SQL conditions in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

I need to convert existing BO reports into Qlikview

There are some simple conditions mentioned in where clause of SQL like ( SELECT col1,col2 from table where table.col1='A' )ould

Should I implement these conditions at script level or it should be at UI level

Re: SQL conditions in Qlikview

anushree1 — Thu, 04 Aug 2016 09:18:38 GMT

It is always a good practice to move most of the filter and calculation conditions to Script to make the performance better

Re: SQL conditions in Qlikview

Frank_Hartmann — Thu, 04 Aug 2016 09:24:17 GMT

try like that

Load

col1,

col2

FROM

[pathofsource]

where col1='A';

Re: SQL conditions in Qlikview

vinod_nallapaneni — Thu, 04 Aug 2016 09:26:53 GMT

You better implement these conditions at script level.

Perhaps even better would be to SET a variable that would be used by no of SQL queries. So that if you want to change it in the future, you will have to change it once.

Re: SQL conditions in Qlikview

Anil_Babu_Samineni — Thu, 04 Aug 2016 09:28:10 GMT

Logic one,

1) Did you connect to BO Database?

2) If your answer is "YES" then check whether the field is there or not?

3) Then You can use Set operations, Can you please provide more information on this for third point?

Re: SQL conditions in Qlikview

arulsettu — Thu, 04 Aug 2016 09:29:24 GMT

try this

in script:

Load

col1,

col2

from......

where col1='A'

in front end:

sum({<col1={'A'}>}value)

this based on your requirement

Re: SQL conditions in Qlikview

vikasmahajan — Thu, 04 Aug 2016 09:32:06 GMT

You can use set analysis for the same

Select sum(Sales) where year=2015

Sum( {$ <Year = {2015} >} Sales )

Sum ({$<Year ={$(=YearName(max(Date)))},>} Sales)

Hope this helps you

Vikas

Re: SQL conditions in Qlikview

Thu, 04 Aug 2016 09:34:00 GMT

based on your requirement if that filtration is only for few reports you can give in UI part in set expression.

like this =sum({<Col1={'A'}>}Sales)

If you restrict that col1='A' for all reports then it's better to give in script part like

Load col1,

col2,

col3,

sales

where col1='A';

select * from

tablename;

Re: SQL conditions in Qlikview

Thu, 04 Aug 2016 09:41:56 GMT

Hi,

Please see below sample query

SELECT

Table1.Col1,Table2.Col2

FROM

Table1,Table2,Table3

WHERE

Table1.Col1=Table2.Col2

AND Table2.Col3=Table3.Col4

AND

(

Table1.col1 In ('111')

AND

Table1.col2 <> '31-12-1899 00:00:00'

AND

Table1.col4 BETWEEN 'DATE RANGE'

AND

Table2.col5 In ( 'Text' )

AND

(

Table1.col3 Like 'A%'

Table2.col4 Like 'A%'

Table3.col6 Like 'A%'

)

Re: SQL conditions in Qlikview

antoniotiman — Thu, 04 Aug 2016 09:45:14 GMT

Hi,

You can use in QV script the same sintax

Connect to ......

SQL Select col1,col2 from Table where col1 ='A' ;

If You don't need records having col1 <> 'A', then I suggest Level Script.

In script Load is used with File (xls,csv,txt,QVD,etc.) or with Resident Table (already loaded)

Regards,

Antonio

Re: SQL conditions in Qlikview

Thu, 04 Aug 2016 09:48:35 GMT

Hi Antonio,

What do you mean by level script

Re: SQL conditions in Qlikview

antoniotiman — Thu, 04 Aug 2016 09:50:53 GMT

Implement conditions in script (where ), don't need in Front end.

Re: SQL conditions in Qlikview

PrashantSangle — Thu, 04 Aug 2016 09:55:50 GMT

Hi,

You can use complete query as it is in QlikView Script.

you need to developed small data model based on your requirement.

Regards,

Re: SQL conditions in Qlikview

Thu, 04 Aug 2016 10:02:25 GMT

Hi,

I have some outer joins like left outer and right outer as well in some SQL queries

Should I implement those joins as equivalent LEFT and RIGHT JOIN in Qlikview?

Re: SQL conditions in Qlikview

Thu, 04 Aug 2016 10:07:45 GMT

You can implement Sharma. try once

Thanks,

Nandu

Re: SQL conditions in Qlikview

antoniotiman — Thu, 04 Aug 2016 10:09:06 GMT

Yes, You use sintax according Your DB (SQL Server,MySql,etc.)

Re: SQL conditions in Qlikview

Thu, 04 Aug 2016 23:53:39 GMT

Hi,

I am still not sure whether putting condition at script level for a table in SQL WHERE clause should be the correct approach as it can be possible that same table is used in another report without any WHERE condition and both reports are based on same data model