Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Unexpected data conversion from MSSQL to Qlikview

Hello,


I am facing issue while pulling data from MS SQL to Qlikview - Some data values are getting converted to different values.


For example: we have a field [Material ID Orig] '000000000000657499' stored in MS SQL table, a view has been created on this table having new derived field [Material ID] '657499' calculated as below in MS SQL view:


CASE WHEN [Material ID Orig] not like '%[^0-9]%'

      THEN CAST(CAST([Material ID Orig] AS BIGINT) AS VARCHAR(18))

         ELSE [Material ID Orig]

         END AS [Material ID]


When we pull [Material ID] from MS SQL view to Qlikview Extract and store it QVD, '657499' Material ID shows its value as ‘3700-0002-0030’.


Managed to get back its desired value in Qlikview as '657499' by using below script in Qlikview editor:

if(len(KeepChar([Material ID Orig], '1234567890')) = len([Material ID Orig]), Evaluate([Material ID Orig]& '* 1'), text([Material ID Orig]))


Same is happening with few more [Material ID Orig] values like '658326E0' showing as '658326'.


I am guessing this conversion of values is due to datatype being getting converted while pulling data from MS SQL to Qlikview.


Please let me know how can this avoided since i don't want to re-calculate this field in Qlikview. Thank you.

0 Replies