Load Sentence changes SQL values

[sebastiandperei]

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!!

[erichshiino]

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

[sebastiandperei]

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!!!

[sebastiandperei · Accepted solution]

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.

[erichshiino]

Hi,

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

T:

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

[sebastiandperei]

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?

[erichshiino]

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

[sebastiandperei]

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