Solved: Table Box - Remove Certain Data - Qlik Community

Report Inappropriate Content · ‎2012-08-01

Hi,

I am New to Qlikview and I have ran a SQL command to bring all data from a specific table using an ODBC Connection.

I used a Table Box to show all my data, however in the Table Box, I only want to show data that has field name closedby with NULL Values.

Therefore I do not want the data to show if the file name closedby hs any data in it.

Regards,

Jon Ditchfield

jonathandienst · ‎2012-08-03

Hi

First off, you cannot use a Table Box to do what you require as the Table Box does not allow expressions. Use a straight table "chart" object:

Dimension: cust_name (and any other dimension fields as required)

Expression: If(Len(closedby) = 0, 1, 0)

In the chart properties, ensure "Suppress Zeroes" is checked. You can also hide the expression in chart properties | presentation - select the expression column, and click Hide Column.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

sivarajs · ‎2012-08-01

use isnull(closedby,0) while loading into qlikview

Report Inappropriate Content · ‎2012-08-01

Thanks for the reply Sivaraj,

I need all the data loading into Qlikview as I will be using the data elsewhere. I just need for this table box to show only NULL values for the filed closedby.

sivarajs · ‎2012-08-02

am not sure that we can calculate expression in table box

use if(isnull(closedby),closedby) as newfield in script and keep closedby as it is

Use newfield in tablebox to show null values

senpradip007 · ‎2012-08-02

Hi Jon,

you can use Where Len(LTrim(RTrim(closedby)))=0 at the time of loading.

Report Inappropriate Content · ‎2012-08-02

Where abouts would I put this in, because if I enter this in my where statement of my SQL code, I get a message back saying it is incorrect syntax.

Report Inappropriate Content · ‎2012-08-02

Hi,

Where would I put this in, because If I enter it in my SQL Query on the Script page, I get a Syntax error. The code I have got currently is:

select

callref,

priority,

owner,

cust_name,

logdate,
assigndate,
closedby,
closedate,
probcode,
fixcode,
respondby,
fixby,
withinresp,
withinfix,
completeby,
compltdate,
lastactdate,
lastactby

from opencall

where suppgroup IN ('FINANCIAL SYSTEMS','BUSINESS DEVELOPER')

order by callref desc

MayilVahanan · ‎2012-08-02

Hi

Try like this,

Load *,if(Len(Trim(Closedby)) = 0, Closedby) as ClosedbyZero;

select

callref,

priority,

owner,

cust_name,

logdate,
assigndate,
closedby,
closedate,
probcode,
fixcode,
respondby,
fixby,
withinresp,
withinfix,
completeby,
compltdate,
lastactdate,
lastactby

from opencall

where suppgroup IN ('FINANCIAL SYSTEMS','BUSINESS DEVELOPER')

order by callref desc

Hope it helps..

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Report Inappropriate Content · ‎2012-08-02

Hi Mayil,

Unfortunately this didnt work, a message occurs when reloading my data saying:

Field not found - <closedby>

MayilVahanan · ‎2012-08-02

Hi

Can you able to paste your script, which you used ?

Load *, if(Len(Trim(closedby)) = 0, closedby) as ClosedbyZero;

Select * from tablename;

Give error?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.