topic LEN function in SQL in QlikView

LEN function in SQL

Mon, 16 Mar 2015 10:54:53 GMT

Hi Guys,

Can anyone think of a reason why the Len function doesn't work in SQL I'm using LEN(fieldname) just keeps telling me the function does not exist 😕

Re: LEN function in SQL

awhitfield — Mon, 16 Mar 2015 11:48:06 GMT

An Example of the syntax to use is as follows:

Copy

SELECT LEN(FirstName) AS Length, FirstName, LastName 
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';
GO

Re: LEN function in SQL

awhitfield — Mon, 16 Mar 2015 11:48:40 GMT

What's your actual select query?

Re: LEN function in SQL

peter_turner — Mon, 16 Mar 2015 12:00:17 GMT

Hello Sarah,

That should work ok (works in my SQL 2008, and i think its the same in 2012)

For example:

Your_QV_Table:

Load

LenOfField,

Other_Fields

;

SQL SELECT

len(YourField) as LenOfField,

Other_Fields

From YourTable;

You can always make your query in the SQL Management Console to test it first, and then copy/paste into QV script.

Re: LEN function in SQL

buzzy996 — Mon, 16 Mar 2015 12:00:55 GMT

in sql server --LEN (Transact-SQL)

in qlikview ---

len( s ) Length of string s. The result is an integer.

Example:

len( Name ) where Name = 'Peter' returns 5.

check ur datatype.

Re: LEN function in SQL

Mon, 16 Mar 2015 12:02:44 GMT

thankyou for helping the query is this -

SQL SELECT

   tmp_oap.orderatomid as service_id,


   if(tmp_ap.flagged_parameter=1,orderatomparam_displayval(tmp_oap.orderatomparamid),'') as parameter_flag1

//   if(tmp_ap.flagged_parameter =1, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag1,
//   if(tmp_ap.flagged_parameter =2, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag2,
//   if(tmp_ap.flagged_parameter =3, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag3,
//   if(tmp_ap.flagged_parameter =4, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag4,
//
//   if(tmp_ap.flagged_parameter =5, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag5,
//
//   if(tmp_ap.flagged_parameter =6, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag6,
//        if(tmp_ap.flagged_parameter =7, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as   parameter_flag7
//

        FROM
            orderatomparams tmp_oap
            INNER JOIN atomparams tmp_ap ON (tmp_ap.atomparamid = tmp_oap.atomparamid)
            INNER JOIN orderatoms oa ON (tmp_oap.orderatomid = oa.orderatomid)
        WHERE
            tmp_ap.flagged_parameter>='1'
           AND tmp_oap.orderatomid = oa.orderatomid
           AND oa.daterecognised > '01/03/2015'
           AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL

GROUP by tmp_oap.orderatomid, tmp_ap.flagged_parameter,tmp_oap.orderatomparamid
;

I was looking to have a table like this

Serviceid , Parameter 1 , Parameter 2 Parameter 3

ID1 (NULL VALUES)

ID1 Address

For some reason for each parameter it was giving me an extra line for null values do you know how to get around this ?

Thanks

Sarah

Re: LEN function in SQL

Mon, 16 Mar 2015 12:09:46 GMT

Re: LEN function in SQL

buzzy996 — Mon, 16 Mar 2015 12:09:49 GMT

can u comment this line and try,

AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL

Re: LEN function in SQL

Mon, 16 Mar 2015 12:10:17 GMT

Heres an example of the error message im getting when I use len I haven't come across this one before 😕

Re: LEN function in SQL

PrashantSangle — Mon, 16 Mar 2015 12:16:46 GMT

Hi,

Add below in where clause

1=1

Try below

SQL SELECT

tmp_oap.orderatomid as service_id,

if(tmp_ap.flagged_parameter=1,orderatomparam_displayval(tmp_oap.orderatomparamid),'') as parameter_flag1

// if(tmp_ap.flagged_parameter =1, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag1,
// if(tmp_ap.flagged_parameter =2, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag2,
// if(tmp_ap.flagged_parameter =3, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag3,
// if(tmp_ap.flagged_parameter =4, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag4,
//
// if(tmp_ap.flagged_parameter =5, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag5,
//
// if(tmp_ap.flagged_parameter =6, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag6,
// if(tmp_ap.flagged_parameter =7, orderatomparam_displayval(tmp_oap.orderatomparamid),'-') as parameter_flag7
//

        FROM
            orderatomparams tmp_oap
            INNER JOIN atomparams tmp_ap ON (tmp_ap.atomparamid = tmp_oap.atomparamid)
            INNER JOIN orderatoms oa ON (tmp_oap.orderatomid = oa.orderatomid)
        WHERE
            tmp_ap.flagged_parameter>='1'
          AND tmp_oap.orderatomid = oa.orderatomid
          AND oa.daterecognised > '01/03/2015'
          AND NOT orderatomparam_displayval(tmp_oap.orderatomparamid)IS NULL

AND 1=1

GROUP by tmp_oap.orderatomid, tmp_ap.flagged_parameter,tmp_oap.orderatomparamid
;

Regards,

Re: LEN function in SQL

Mon, 16 Mar 2015 12:40:02 GMT

Re: LEN function in SQL

Mon, 16 Mar 2015 12:40:43 GMT

Thanks Max_dreamer I think its getting there I don't understand why its giving me two lines what do you think could be causing it ?

Re: LEN function in SQL

PrashantSangle — Mon, 16 Mar 2015 12:47:01 GMT

Hi,

When you use Join then there is possibilty of creating extra line,

Therefore when you use join in SQL you must use

1=1 which gives data only get true value.

Regards

Re: LEN function in SQL

happydays1967 — Mon, 16 Mar 2015 12:50:02 GMT

What 'dialect' of SQL are you using? i.e. what is the database engine? Some dialects use len() others length(). Might be just as simple as that

Re: LEN function in SQL

Mon, 16 Mar 2015 12:56:32 GMT

Thanks I've put in code as above but is still giving me two lines, not sure why.

Sorry about this !

Re: LEN function in SQL

buzzy996 — Mon, 16 Mar 2015 12:59:46 GMT

can u try, on dimension tab check the check box Suppress When the value is null?

Re: LEN function in SQL

Mon, 16 Mar 2015 13:12:01 GMT

Im not using dimentions on this table having the same problem still anyone got any ideas ?

Re: LEN function in SQL

Mon, 16 Mar 2015 13:21:42 GMT

For example I'm getting this at the moment but I'm trying to show on one row - When I select don't show null values it is hiding the entire row so nothing is showing 😕

Re: LEN function in SQL

happydays1967 — Mon, 16 Mar 2015 13:25:34 GMT

load

service_id

MaxString(parameter_flag1) as parm_1,

MaxString(parameter_flag2) as parm_2,

......

resident <your_table>

GROUP BY service_id

Tha will put all values on 1 line

Regards,

Re: LEN function in SQL

Mon, 16 Mar 2015 13:29:09 GMT

Thanks Hans there's a lot of linking statements is there a way I can do that without loosing that, I tried maxstring in the script and it returns function not recognised 😕 but seems to work in front end