topic Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW in QlikView

SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 06:48:49 GMT

hello ,

Sql server stored procedure is called from qlikview report and specifically one value of a column is getting converted from 401 to 401.0 during load execution ..

tried to CAST all the values to a string of that column in stored procedure , but still it is coming as 401.0 , wanted to come as 401

confused , why such thing is happening.

can somebody throw light on this , please

Many Thanks

Donesh

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Kushal_Chawda — Fri, 07 Aug 2015 06:55:10 GMT

Try load num(YourFueld) as YourField in qlikview script

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Colin-Albert — Fri, 07 Aug 2015 06:59:03 GMT

The QlikView floor() command will round the number down to an integer.

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 07:02:55 GMT

hi again ,

all the value that is coming from that column invoked by stored procedure is not number only , it has alphanumeric value as well like V57.87, 401, 556.899, V34.5 etc

Cheers

Donesh

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Kushal_Chawda — Fri, 07 Aug 2015 07:09:25 GMT

So what do you want to do? want to remove alphanumerical values?

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 07:12:19 GMT

No , absorb the same ditto value as it is in the database.

in database value is coming as 401 , why it is converting to 401.0 in qlikview when called from a stored procedure.

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 08:30:42 GMT

Hi All,

Please correct my statement .

there was 401.0 in earlier database rows which got stored in Qlikview memory , and when 401 came , do Qlikview checked that 401.0 is already existing and discarded 401 . is Qlikview able to create a separate memory for 401.0 and 401 separately for each ?

Please explain.

Cheers

Donesh

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

jonathandienst — Fri, 07 Aug 2015 08:35:25 GMT

Qlikview (like Excel) tries to determine the data type from the first value loaded. If it looks like a number, it will be loaded as a number and for one reason or another QV selected a format of 0.0. Numerically 401 and 401.0 are the same value.

If this is a key or an ID, you prefer it to be text, in which case load it with a text() statement like this:

LOAD text(ID) as ID,

....

This will treat 401 and 401.0 as two distinct entries.

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 08:40:06 GMT

Hi Jonathan

am loading the value from SQL Server stored procedure , Load statement will not come .

rather am using in load

SQL EXECUTE <stored procedure>

tried converting inside the stored procedure to string , but alas, no fruitful result.

is there any work around ?

thanks

Donesh

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

jonathandienst — Fri, 07 Aug 2015 08:44:01 GMT

Use a preceding load:

LOAD text(ID) As IDVal, *;

SQL Exec <stored proc>;

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 09:28:47 GMT

Hi Jonathan ,

it worked , spot on . Many Thanks

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

sasiparupudi1 — Fri, 07 Aug 2015 09:29:56 GMT

LOAD if (IsNum(ID),round(ID),Text(ID))) As ID, *;

SQL Exec <stored proc>;

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

Fri, 07 Aug 2015 09:32:00 GMT

hi Sasidhar,

one thing , in preceding load for stored procedure , you have to consider all fields separately , * is not working giving ODBC read failed error.

Cheers

Donesh

Re: SQL server stored procedure column value is changing from 401 to 401.0 when loaded on to QLIKVIEW

sasiparupudi1 — Fri, 07 Aug 2015 10:12:28 GMT

Cool, please close this thread by marking the correct answer