topic Re: Sql loading incorrect data on one item in QlikView

Sql loading incorrect data on one item

adamdavi3s — Wed, 09 May 2012 14:37:36 GMT

Hi All,

This has me stumped.

I've written many applications and never come across a problem like this....

I am loading a load of static product information, however on one field and only for one product, it is adding a preceeding 0, and thus duplicating the field.

This is the select statement run in SSMS (obviously limited):

SELECT 
article,
art_descr,
art_gr_id,
catalogue,
contract,
user_id,
period,
apar_id,
created_date,
sup_article,
unit
FROM qlikview.proc_masterfile_products
where article LIKE '%4276%'
results:

Here is the QV code and results:

article    art_descr    art_gr_id    catalogue    contract    user_id    period    apar_id    created_date    sup_article    unit
04276                        BATTERY POWERSONIC PS1208 (AMP CONN)                                                                                                                                                                                                                               PNA                          SDH00700    SDH00700    SQUIRS0R    201002    108118    2010-05-20    04276                                                 EACH. CARRIAGE £12.30
4276    1/2 GAMMON APPROX.3KG.    AFD    SDH01215    SDH01215    ROACS98    201201    111196    2012-04-19    4276                                                  EACH

products:

LOAD

article AS %product,
article as prod_product,
art_descr AS product_description,
art_gr_id AS %eclass,
catalogue as %catalogue,
catalogue,
contract as %contract,
contract,
user_id as prod_user_id,
period as prod_period,
apar_id as prod_apar_id,
created_date as prod_date,
sup_article as prod_sup_article,
unit as prod_unit;

SQL SELECT distinct
article,
art_descr,
art_gr_id,
catalogue,
contract,
user_id,
period,
apar_id,
created_date,
sup_article,
unit

FROM qlikview.proc_masterfile_products

where article LIKE '%4276%';

For some reason its allocating the same product code to both lines....

I have attached the test file which is a straight cut from my live file.

edit: I have tried casting and converting the data in case qlikview thought it was an excel date field or something daft like that.... I am stumped!

Sql loading incorrect data on one item

adamdavi3s — Thu, 10 May 2012 08:08:57 GMT

Hmm I managed to resolve this issue by wrapping the field in TEXT() in the qlikview part of script.

What I can't understand is WHY I need to do this....

Re: Sql loading incorrect data on one item

varunpbhandary — Thu, 10 May 2012 12:03:25 GMT

Hi addamdavi3s,

Have you tried checking the field's format in DB?

also you can try using the Convert(Target Data Type,Field) function in SQL Select Statement to convert the field you are picking up to text

e.g Select convert(Varchar(20),ItemNumber) From Table;

Qlikview Auto interprets the field as a number and hence 04976 and 4976 is the same for it, It needs to be explicitly told to consider the formatting of the field as text.

Cheers!

Varun Bhandary

Re: Sql loading incorrect data on one item

adamdavi3s — Thu, 10 May 2012 12:09:04 GMT

Hi Varun,

The field in the DB is varchar as it contains alphanumeric data, so its strange!

I did try converting and casting the field as varchar in the load script, again but that had no impact in this case, obviously all the data with alpha characters were fine

Once I explicitly told qlikview the field was text then that seemed to resolve it!