topic Table Box - Remove Certain Data in QlikView

Table Box - Remove Certain Data

Wed, 01 Aug 2012 11:13:20 GMT

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

Re: Table Box - Remove Certain Data

sivarajs — Wed, 01 Aug 2012 12:44:27 GMT

use isnull(closedby,0) while loading into qlikview

Re: Table Box - Remove Certain Data

Wed, 01 Aug 2012 12:53:38 GMT

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.

Re: Table Box - Remove Certain Data

sivarajs — Thu, 02 Aug 2012 04:05:26 GMT

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

Re: Table Box - Remove Certain Data

senpradip007 — Thu, 02 Aug 2012 06:26:14 GMT

Hi Jon,

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

Re: Table Box - Remove Certain Data

Thu, 02 Aug 2012 15:00:04 GMT

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.

Re: Table Box - Remove Certain Data

Thu, 02 Aug 2012 15:01:27 GMT

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

Re: Table Box - Remove Certain Data

MayilVahanan — Thu, 02 Aug 2012 15:19:22 GMT

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..

Re: Table Box - Remove Certain Data

Thu, 02 Aug 2012 15:30:47 GMT

Hi Mayil,

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

Field not found - <closedby>

Re: Table Box - Remove Certain Data

MayilVahanan — Thu, 02 Aug 2012 15:36:30 GMT

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

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

Select * from tablename;

Give error?

Re: Table Box - Remove Certain Data

Fri, 03 Aug 2012 09:33:03 GMT

Hi,

That has loaded in without error, however when I created a tablebox, and set one of the fields to ClosebyZero, is basically sets everything to NULL.

In my Opencall Table, I have about 4 items that the closedby value is set to NULL (meanning that the issue has not been closed by anyone). All I want to do is create a Tablebox that will only report on those where the closedby is set to NULL. Therefore my tablebox should only show 4 rows.

Re: Table Box - Remove Certain Data

jonathandienst — Fri, 03 Aug 2012 10:14:00 GMT

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

Re: Table Box - Remove Certain Data

muniyandi — Fri, 03 Aug 2012 10:41:50 GMT

Hi,

Worte your sql:

select callref,priority,owner,cust_name,logdate,assigndate,closedby,closedate,probcode,fixcode,respondby,fixby,
withinresp,withinfix,completeby,compltdate,lastactdate,lastactby ,case when trim(closedby) is null then 'yes' else 'no' END AS clflag

from opencall

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

order by callref desc

Next : your qlikview expression are if(clflag='yes',1,0)

Re: Table Box - Remove Certain Data

Fri, 03 Aug 2012 14:23:16 GMT

Thanks Jonathan, it is working as I want it to.

Also thanks for your help to Muni.