topic how to exclude a value in textbox in QlikView

how to exclude a value in textbox

raghavsurya — Tue, 28 Dec 2010 10:38:02 GMT

Hi All,

We currently have a situation wherein the graphs and charts in one of the tab does not have details for one of dimension value present in a list box. Hence we want to hide the value in the list box. Is there a way to do it.

Can anybody help me.

Regards,

Raghav

how to exclude a value in textbox

jonathandienst — Tue, 28 Dec 2010 10:43:02 GMT

Raghav

I am assuming that you have one or more blank/null values in the list box and you want to hide these.

If so, then select <expression> as the field in the list box, and enter something like this as the expression:

if(len(field)>0, field)

Hope that helps

Jonathan

how to exclude a value in textbox

tresB — Tue, 28 Dec 2010 10:48:07 GMT

Hi raghav,

Go to <expression> in the list box option and write the expression as:

If([Your Filed]<>'Your Particlular Value which you want to hide',[Your Field])

regards, tresesco

how to exclude a value in textbox

raghavsurya — Tue, 28 Dec 2010 10:57:21 GMT

Hi Jonathan,

To be elaborate, I want to hide the Year 2011 in the Year list box. The current sheet has graphs for actual values and hence no graph will display if Year 2011 is selected.

Can u pls suggest how to do it

Thanks and Regards,

Raghav

how to exclude a value in textbox

raghavsurya — Tue, 28 Dec 2010 11:01:41 GMT

Hi Tresesco,

I want to hide the year 2011 in list box called 'Year'. Can you please suggest where can I find the expression in List box properties.

Thanks and Regards,

Raghav

how to exclude a value in textbox

syed_muzammil — Tue, 28 Dec 2010 11:05:29 GMT

Hi,

At the script level you can use the Where exists() function in your Year Table to remove those years which donot have data.

Eg:

Where exists(MainDate,Date) where MainDate is from the table you are linking your Year Table to.

I hope you understood this else check for Exisits() in the help maual of Qlikview

how to exclude a value in textbox

raghavsurya — Tue, 28 Dec 2010 11:10:56 GMT

Hi Syed,

I need to exclude the Year 2011 from the list box for only one tab. But I need the year to be included in the list box for other sheets.

Regards,

Raghav

how to exclude a value in textbox

syed_muzammil — Tue, 28 Dec 2010 11:16:47 GMT

Hi,

Expression can be found in the Properties-->General-->Field list in the List Box. It will be the in the last row of the Filed selection drop down. There you can write your expression.

how to exclude a value in textbox

tresB — Tue, 28 Dec 2010 11:17:17 GMT

Hi,

Try: right Click on sheet -> New Sheet Object ->List Box . In general Tab you will find "field" where all list of field are there. DON'T select "year" from there. SELECT <Expression>(you will find in the list). Write the formula over there. Done!

Formula: If (Year<>2011,Year)

regards, tresesco

how to exclude a value in textbox

jonathandienst — Tue, 28 Dec 2010 11:18:19 GMT

Raghav

Use this as the expression then to one of:

if(SalesYear <= 2010), SalesYear)
if(SalesYear <= Year(Max(SalesDate)), SalesYear)

Adjust the field names to suit your model. The second example will allow it to be determined dynamically.

Jonathan

how to exclude a value in textbox

Tue, 28 Dec 2010 11:47:20 GMT

Hi ,

Answer suggested by Tresesco is almost right but instead of using 2011 has hardcode you need to check the value in any of you key which doesn't have the data for future like ur key or the transaction value.. hence the formula can be

If(Len(TransactionAmount)>0,Year)

Note : TransactionAmount is just an asumption of the field.. Rest follow the steps suggested by Tresesco..

how to exclude a value in textbox

sathishkumar_go — Tue, 28 Dec 2010 12:13:05 GMT

HI Raghavsurya,

If i understood ur problem correctly,

then try this,

in load script itself restrict the year 2011 in year field and put alias some other name.

then in particular tab call that field in year.

Ex:

fiscal Cal:

year,

if(year <> 2011,year) as year_New

so in ur table u have 2 year fields 1 is containing 2011 and 1 is not 2011.

Regards

Sathish

how to exclude a value in textbox

raghavsurya — Tue, 28 Dec 2010 12:18:28 GMT

Thanks a lot for your kind help.

Regards,

Raghav