topic Re: Leading zeros and spaces in QlikView

Leading zeros and spaces

kulasekhar — Mon, 19 Jun 2017 13:29:42 GMT

Hi guys.

I need help on an issue. I have a view in sql server that brings two records 539681 17 and 0053968117.

These are two different items.When i make a select from that view on a qvw the result is that it brings only one of the records.

I have tried Text(ITEM_NUMBER) but it didnt work. Can anyone help?

See below Database Tale:

ITEM_NUMBER, BASELINECUT

0056729003, TERMINATED

56729003SUB1, IN_PRODUCTION

539681 17, IN_PRODUCTION

0053968117, TERMINATED

063230100511, TERMINATED

63230100511, IN_PRODUCTION

I want show data same as database with leading zeros, spaces and any caraters(56729003SUB1).

Thanks

Sekhar

Re: Leading zeros and spaces

avinashelite — Mon, 19 Jun 2017 13:35:24 GMT

Did you tried the text(ITEM_NUMBER) in the script ??

Re: Leading zeros and spaces

kulasekhar — Mon, 19 Jun 2017 13:48:30 GMT

Hi Avinash,

yes i had tried but it's not working mine.

Thanks

Sekhar

Re: Leading zeros and spaces

vishsaggi — Mon, 19 Jun 2017 13:50:28 GMT

When I run this i get all the values for ITEM_NUMBER in listbox check:

LOAD Text(ITEM_NUMBER) AS ITEMNUM,

BASELINECUT;

LOAD * INLINE [

ITEM_NUMBER, BASELINECUT

0056729003, TERMINATED

56729003SUB1, IN_PRODUCTION

539681 17, IN_PRODUCTION

0053968117, TERMINATED

063230100511, TERMINATED

63230100511, IN_PRODUCTION

];

Re: Leading zeros and spaces

kulasekhar — Mon, 19 Jun 2017 14:07:54 GMT

Hi All,

How Can i add QVW this link.

Thanks

Kulasekhar

Re: Leading zeros and spaces

avinashelite — Mon, 19 Jun 2017 14:13:56 GMT

Click on the Use advance editor > then to bottom right you will see the attach option

I feel while fetching from the DB itself it getting truncated

add the Text(ITEM_NUMBER) in the SQL query itself

Re: Leading zeros and spaces

kulasekhar — Mon, 19 Jun 2017 16:38:01 GMT

Hi All.

I have attached QVD and QVW below URL. Please help me.

Leading spaces and zeros

Thanks

Sekhar.

Re: Leading zeros and spaces

vishsaggi — Mon, 19 Jun 2017 17:06:32 GMT

As Avinash Suggested,

Did you try using Text(ITEM_NUMBER) in the script that generates this QVD.

and Sunny explained in this link you sent, that we cannot find the number 0053968117 in the QVD. Can you check that?

Can you send us the script that generated this QVD?

Re: Leading zeros and spaces

passionate — Tue, 20 Jun 2017 06:59:26 GMT

Hi Babu,

You can use replace(ltrim(replace(MyField,'0',' ')),' ','0') AS MyField

To trim leading zeros

Regards,

Pankaj