topic Re: A record is different between sql server and qlik sense in App Development

A record is different between sql server and qlik sense

coco — Tue, 21 Sep 2021 09:05:53 GMT

Read the code column from the database, and the record displayed as 0134 in the database (as shown in pic1) becomes 00134 in qlik sense(as shown in pic2) . Why? I'm sure this is the same record and the customer name is same.

Here is my coding,where should be modified?

LOAD *;

SQL

with t1 as(

select head.cwdzcode as code

from CP_kehu as head)

select code

from t1;

Re: A record is different between sql server and qlik sense

AshutoshBhumkar — Tue, 21 Sep 2021 11:07:01 GMT

Hi, Not sure why this is happening.

My question is whether you want to remove those leading zeros or want to keep them as it is.

Re: A record is different between sql server and qlik sense

Vegar — Tue, 21 Sep 2021 13:43:11 GMT

You could handle the field as a string/text. To do so you need to load the field using text() from the database, like this.

LOAD text(code) as code, zhongwenmingcheng, etc... ; SQL with t1 as( select head.cwdzcode as code from CP_kehu as head) select code from t1;

If you know that your code always is four (4) digits long then you also could use num() to format it like this.

LOAD num(code,'0000') as code, zhongwenmingcheng, etc... ; SQL with t1 as( select head.cwdzcode as code from CP_kehu as head) select code from t1;

Re: A record is different between sql server and qlik sense

coco — Tue, 21 Sep 2021 13:54:34 GMT

I hope the data in qlik should be consistent with the database, so it should be 0134 in qlik, not 00134. So do you know what the problem is? How to make the record in qlik 0134?

Re: A record is different between sql server and qlik sense

coco — Tue, 21 Sep 2021 14:12:11 GMT

Thank you very much!😃 I have got it.

Do you know how to convert a string to nvarchar type in qlik?

Because I always encounter the problem that Chinese cannot be displayed in qlik, I need to convert the Chinese string to nvarchar(300) type first.I only know how to convert Chinese strings to nvarchar types in the database like this CAST('你好' as nvarchar(300)) .How to implement in qlik?

Re: A record is different between sql server and qlik sense

PrashantSangle — Tue, 21 Sep 2021 15:34:39 GMT

if you are loading only one field from table then why you are using with (CTE)

Just use below query

SQL select cwdzcode as code
from CP_kehu;

answer to your 2nd question is you can same query which work in sql editor.

Qlik don't have have datatypes.

Regards,

Prashant Sangle