topic Load Sentence changes SQL values in QlikView

Load Sentence changes SQL values

sebastiandperei — Fri, 15 Jun 2012 22:11:42 GMT

Hi all.

I have to read the field "survey_sk" and "fronts" from SQL2005 database. "survey_sk" is an integer of 18 positions. If I use:

SQL Select

survey_sk,

fronts

From Table

where survey_sk='634610963568975007';

QV Loads the registers with this survey_sk (i know with the sum(fronts) expression), but the survey_sk field is empty. Now, if I try;

Load

num(survey_sk,'##################') as survey_sk

SQL Select

survey_sk,

fronts

From Table

where survey_sk='634610963568975007';

The same data is loaded, but survey_sk='63461096356897 4980' (instead of 63461096356897 5007)

Now, if I put the where in Load instead of Select, QV doesn't find any register. Is Clear that Load sentence modifyes the data.

Any suggestion to load correct data? Im using QV11.00.11282.0 SR1

Thanks!!

Re: Load Sentence changes SQL values

erichshiino — Sat, 16 Jun 2012 03:15:31 GMT

Hi, QV works with a limit of 14 digits for a number.

You can get some ways to handle it here:

http://community.qlik.com/message/223100#223100

I guess you can also try this:

Load

evaluate(survey_sk) as survey_sk

SQL Select

survey_sk,

fronts

From Table

where survey_sk='634610963568975007';

Hope it helps,

Erich

Re: Load Sentence changes SQL values

sebastiandperei — Sat, 16 Jun 2012 22:28:49 GMT

Thanks Erich!, but I don't care if QV takes this field as Text... In fact, I have tested it with Text().... And nothing happens...

"Evaluate()", will give me the field as Integer? And in this Integer, what about the 14 digit limit? This Integer, is in normal format or in scientific notation?

Thanks!!!

Re: Load Sentence changes SQL values

sebastiandperei — Mon, 18 Jun 2012 18:12:14 GMT

Finally, i used:

Load *;

SQL Select

CAST (survey_sk as Char) as survey_sk,

fronts

From Table;

As this way, QV reads the field with correct content, as Text, but correct.

Re: Load Sentence changes SQL values

erichshiino — Tue, 19 Jun 2012 13:27:31 GMT

Hi,

If you try the following script, you will create two fields:

LOAD * INLINE [

Test

123456789123456789

];

LOAD Evaluate(Test) as EvalTest, Test resident T;

drop table T;

The field Test is just a string but EvalTest is a number. If you include it in a list, you can see all the 18 digits, but when you write some expression with it, you will get the scientific notation (e.g. sum(EvalTest) ). So, I believe you are actually losing precision due to this notation, although QV can keep the original format when presenting just the field.

Regards,

Erich

Re: Load Sentence changes SQL values

sebastiandperei — Tue, 19 Jun 2012 14:06:40 GMT

Erich, Thanks for your interest in this topic.

In my case, I don't want to make expressions with this field, and because of it is better to have it as string.

But, if we would need like a number, this thread could be useful for the comunity. So, I tryed with your suggestion, and the field has only Nulls.

I think that the problem is in the transition of SQL Select to Load, because the "where" clause works good, the Load sentence keeps taking the field like a number and, because of its >14 digits, couldn't take like that.

So, I can't understand why works well since the data comes from INLINE, and not from Select sentence.

I have checked the SQL base, and the field is "BigInt" type. Using "CAST", I format it to "Char" type. I can now take it like number, with Evaluate or simply Num function. Did you used Evaluate with SQL data?

Re: Load Sentence changes SQL values

erichshiino — Tue, 19 Jun 2012 14:57:19 GMT

Hi, I used before, but I'm not sure the original database datatype.

It's more difficult when there are different results in a inline and a real database...

As a last suggestion, could you please try something like this:

LOAD Evaluate(text(Test)) as EvalTest, Test resident T;

Best Regards,

Erich

Re: Load Sentence changes SQL values

sebastiandperei — Tue, 19 Jun 2012 17:10:31 GMT

Yes Erich, i tryed this but is the same: Load doesn''t receive data from Select.

But, Load isn't the problem, because using CAST in Select, any function in Load works right