topic Re: Left,Right,Index in QlikView

Left,Right,Index

timsaddler — Mon, 03 Sep 2012 14:55:59 GMT

Hi All

Need to extract data from a field being pulled from a sql server 2005 db - whatever i try doesn't work!

Any suggestions gratefully received. Script below

SQL

SELECT
T1.ID,
T1.Mon_ID,
T3.Title,
T1.Reading_Date,
T1.Site_ID,
T2.Name,
Left(T2,Name,3),
T1.Value

FROM "Envoy_Live".dbo.Mon_Reading T1

inner join "Envoy_Live".dbo.Site T2
on T1.Site_ID = T2.ID

inner join "Envoy_Live".dbo.Mon T3
on T1.Mon_ID = T3.ID

where Year(T1.Reading_Date) = 2012
and T3.ID in (948, 947, 946, 945, 944, 943, 951, 950, 949)

Re: Left,Right,Index

Anonymous — Mon, 03 Sep 2012 15:04:01 GMT

You cannot use QV functions in SQL - it doesn't understand... Use preceeding load, where you can use QV functions:

LOAD
...
Left(Name,3) as ...,
...
;
SQL
SELECT
T1.ID,
T1.Mon_ID,
T3.Title,
T1.Reading_Date,
T1.Site_ID,
T2.Name,
T1.Value

Regards,
Michael

Re: Left,Right,Index

timsaddler — Wed, 05 Sep 2012 13:52:59 GMT

Thanks Michael - that pointed me in the right direction to correct my mistake.