topic Re: Missing Values in QV Document in QlikView

Missing Values in QV Document

PraveenNavara — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am connecting to SQL Server Source , extracting data from table with 1 column(nvarchar) and 2 records (123 , 0123) using Select statement with out any filters.

When i use list box i only get 0123 not 123. Can you please help me here.

Regards

Praveen

Re: Missing Values in QV Document

m_woolf — Tue, 28 May 2019 19:06:38 GMT

It's difficult to answer without seeing your load script and your data. Here'a a guess. Go to your listbox properties and change presentation|alignment|numbers to left.

Re: Missing Values in QV Document

PraveenNavara — Tue, 28 May 2019 19:15:15 GMT

Hi ,
This is my Load Script
Item_Filter:
SQL SELECT
*
FROM [DM_SER].[Dim].[Item] // Fetches 37 Million Records out of which 135161 has 18 records and 0135161 has 20 records

;

ItemTmp_Filter:
LOAD
RowNo() as rowid,
Master_ItemNumber as Item_Filter ,
Master_ItemName as ItemName_Filter
Resident Item_Filter
where text(rtrim(ltrim(Master_ItemNumber)))='135161'; // Even then its giving only 0135161 in list box not actual filtered value

drop table Item_Filter;

Re: Missing Values in QV Document

Colin-Albert — Wed, 29 May 2019 11:07:46 GMT

When Qlik loads your data, it reads the first value 0123, and recognises this as an integer 123 and stores the data as 0123.

When the second value 123 is loaded, Qlik recognises that data for 123 already exists, so stores the same value again for the second row. Hence you only see the value 0123 for both rows that are loaded in your Qlik data.

You either need to cleanse your data, or if you want 0123 and 123 stored as separate values, use the text() function in your load script.

text(columnX) as columnX

Re: Missing Values in QV Document

Colin-Albert — Wed, 29 May 2019 11:12:01 GMT

Try this...

Item_Filter:

RowNo() as rowid,
text(Master_ItemNumber) as Item_Filter ,
Master_ItemName as ItemName_Filter ;

SQL SELECT
*
FROM [DM_SER].[Dim].[Item]

Re: Missing Values in QV Document

PraveenNavara — Wed, 29 May 2019 12:55:33 GMT

Thank You. It Worked