topic Re: List Box Problem in QlikView

List Box Problem

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

Hello Community,

In my excel, i have like

Ename, Sal

A, 10

B, 20

C, 30

While i ma fetching this data to list box in Qlikview. I got three names but, I am getting 1 more space space also will come. Can you assist me please

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 07:14:03 GMT

I think you have the empty cell in the excel ....try to impose a condition to fetch the data where it is not null

like where Len(trim(Ename))>0

Re: List Box Problem

Anil_Babu_Samineni — Mon, 22 Aug 2016 07:17:57 GMT

I've use only Trim(Ename) as Ename. Do i need to change this?

Ename

Re: List Box Problem

ramasaisaksoft — Mon, 22 Aug 2016 07:22:52 GMT

Hi Anil,

Always Len() is useful while you are ignoring Null values .so try to use Avinash thought.

Re: List Box Problem

Anil_Babu_Samineni — Mon, 22 Aug 2016 07:26:39 GMT

If i use this -- Len(trim(Ename))>0

Ename

-1

But, I want to show

Ename, Sal

A, 10

B, 20

C, 30

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 07:26:55 GMT

Yes , if you want to eliminate the null value then you have to update the condition

TRIM will just eliminate the leading and trilling spaces and it won't eliminate the data

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 07:28:40 GMT

could you please share the script and the who your extracting the data ?? while exporting the data your getting this space or while loading the data your getting this space ??

Re: List Box Problem

Anil_Babu_Samineni — Mon, 22 Aug 2016 07:30:47 GMT

See This,

Employee:

LOAD Trim(Ename) as Ename,

Sal

FROM

Employee

(ooxml, embedded labels, table is Sheet1) where Len(Trim(Ename))>0;

Employee:

LOAD Len(Trim(Ename))>0 as Ename,

Sal

FROM

Employee

(ooxml, embedded labels, table is Sheet1);

Re: List Box Problem

ramchalla — Mon, 22 Aug 2016 07:31:26 GMT

Hi Anil,

You can include the below condition in your script as Avinash suggested.

Where Len(Trim(Ename))>0

The script will look like as below.

Directory;

LOAD Ename,

Sal

FROM

a.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(Ename))>0;

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 07:35:52 GMT

It should work

Employee:

LOAD Ename,

Sal

FROM

Employee

(ooxml, embedded labels, table is Sheet1) where Len(Trim(Ename))>0 and Len(Trim(Sal))>0;

if your still getting the null data ,then please share the excel file and QVW will try to debug the same

Re: List Box Problem

Anil_Babu_Samineni — Mon, 22 Aug 2016 08:03:13 GMT

This is Live, Check this and Let me know. For Main sheet i have Furnace - Check why this Space came?

Second Issue

Shee2 -- I have Drill down. If we select then should show associative only. Please choose Plant and see. Why This Others will showing here?

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 08:20:02 GMT

use this condition in the list box

if(len(trim(Furnace))>0,Furnace)

if you want to eliminate the data the script level then use the condition in the script level

Issue2 : The problem is with the Expression what you have written , its not finding any count for the set analysis what you have written hence its not showing the result , to check this just disable the current condition and add a simple expression like 1 then try the drill down

Hope this helps you

Re: List Box Problem

beck_bakytbek — Mon, 22 Aug 2016 08:26:47 GMT

Hi Anil,

You can use whithin your ListBox: check in General: Field: Expression:

and enter this Expression: if(Ename= '-',Null(),Ename)

i hope that helps

beck

Re: List Box Problem

Anil_Babu_Samineni — Mon, 22 Aug 2016 08:34:32 GMT

First one is Resolved and how about Second one?

PFA. Here, Why - Values showing.

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 09:20:26 GMT

Go to chart Properties > Dimensions > check the suppress null values .

This is resolve the second issue

Re: List Box Problem

Anil_Babu_Samineni — Mon, 22 Aug 2016 09:31:50 GMT

Yes, But That is the Second Dimension in Drill Down

Re: List Box Problem

avinashelite — Mon, 22 Aug 2016 09:41:22 GMT

It will apply for the all the dimensions rite ?? please check

Re: List Box Problem

Chanty4u — Mon, 22 Aug 2016 09:44:17 GMT

try this

Alt(Ename,' ') as Ename,

If(Len(Trim(Ename))=0,' ',Ename) As Ename