Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Unexpected data conversion from MSSQL to Qlikview


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.