Value changing after importing from data from SQL Server to Qlikview

[rajivmeher]

Hi All

I am trying to pull data from SQL Server to Qlikview using OLEDB connection. However when the data comes to Qlikview the data is changing. Please suggest if I am doing anything wrong or if there is anyway to avoid it.

The values in SQL Server for the column in Question is NULL, 1, 0. When I load it to Qlikview, it changes to -1 and 0.

Please see the screenshots below for reference.

Please suggest.

Regards

Rajiv.

[vishsaggi]

Can you try this and run

LOAD PORT, RecNo() AS RecNum;

SQL SELECT DISTINCT PORT

FROM dbo.DIM_SUBSCRIPTIONS;

Then add table box and add PORT and RecNum and tell us what you see. ?

[rajivmeher]

Hi vishsaggi‌

Please see the screenshot below for the query:

Regards

Rajiv.

[vishsaggi]

Ok so you are getting the Null record. Try like below in the LOAD

LOAD IF(Len(Trim(PORT)) = 0, 'NULL', PORT) AS PORT, RecNo() AS RecNum;

SQL .....

FROM ....;

[rajivmeher]

Hi vishsaggi‌

Thanks for the feedback. However I guess the code you have suggested will handle the Blank value and show it as Null. However the 1 value will still show as -1.

My requirement is to understand why the value is changing to other value when loaded to Qlikview. Is it a driver issue or any other reason. I am able to handle the values in SQL level by using case statement for NULL, 0 and 1 to other values (see my Qlikview screenshot, where the SQL is commented out). But I want to handle it without doing any script change (else suppose some other columns are doing the same in a query, than it will be difficult to identify the same).

Please suggest.

Regards

Rajiv.

[marcus_sommer]

Are you sure that Port has really "normal" numerical values? Or could it be that it is a boolean value or is treated from the driver as a boolean data-type. In Qlik means 0 always FALSE and each other numerical value is treated as TRUE whereby if TRUE is the result of a condition or an expression like true() it will have the value of -1.

Further I suggest to check how the data are really stored in Qlik because there are scenarios in which the displaying is different from the real value. This meant adding another load after your SQL with something like:

load *, text(fieldvalue('Port', recno())) as Port2 resident YourSQLTable;

- Marcus

[rajivmeher]

Hi marcus_sommer‌

That makes sense. The data in SQL Server column is stored as bit and accepts null. May be because of that 1 is being accepted as True() and converts to -1 and keeps 0 as False or 0.

Is there anyway I can handle this value in Qlikview so that it accepts the values as text or integer instead of boolean?

The Result for load *, text(fieldvalue('Port', recno())) as Port2 resident YourSQLTable; comes back as 0 and -1 for port column and Blank/ dash for the port2 column.

Please suggest further.

Regards

Rajiv.

[marcus_sommer · Accepted solution]

You could try to adjust it within a preceeding load with something like:

Port *-1 as Port

or

fabs(Port) as Port

or

text(Port) as Port

Another try would be to change the ordering of the field-values with an order by statement within the SQL because the data-interpretation in Qlik is determined through the first field-value and then applied to all values. Having this said you could also enforce this interpretation with a dummy like:

dummy: load floor(1) as Port autogenerate 1;

SQL ....

drop tables dummy;

- Marcus

[rajivmeher]

Thanks marcus_sommer‌.

fabs(PORT) worked fine. TEXT(PORT) didn't. As this column is in a big table, it wont be possible to do dummy concatenate. However I will try and see if I can implement order by during load, but I doubt it as other field values may not allow the order by that I am expecting because of the varying value in other columns.

However thanks a lot, fabs will solve my problem for now.

Regards

Rajiv.

[marcus_sommer]

That's great. But you should also try my dummy-suggestion because this isn't a concatenating of the tables else only of the distinct fieldvalues which are stored in systemtables - it should not impact the performance in any way. More background to the internal datahandling of Qlik could you find here: Symbol Tables and Bit-Stuffed Pointers - a deeper look behind the scenes.

- Marcus