topic Question on List box expression in QlikView

Question on List box expression

Tue, 24 Jun 2014 21:09:25 GMT

Hi -

This is a simple question, with I hope a simple answer. I have a Master Calendar with a Trade_Calendar_Quarter column. It is 1, 2, 3, or 4 depending upon in which quarter the date falls. I can create a list box with that column in it, and it shows 1, 2, 3, and 4, as expected. I wanted it to look nice, so I made it an expression:

='Q' & Trade_Calendar_Quarter

With that as the expression, it shows Q, Q1, Q2, Q3, and Q4. The Q is grayed out, as it is not possible.

So the question is really two questions: why does that happen, and how can I not have the Q show up?

Re: Question on List box expression

venkatg6759 — Tue, 24 Jun 2014 21:13:21 GMT

You have hide excluded in properties of that list box if you don't want to show Q .

It was not showed you just made an expression at front end .

Re: Question on List box expression

Tue, 24 Jun 2014 21:14:42 GMT

Hi,

Use this in your master calendar:

Ceil(Month(DateField)/3) AS Quarter,

Best regards.

Re: Question on List box expression

maxgro — Tue, 24 Jun 2014 21:14:53 GMT

=if(len(trim(Trade_Calendar_Quarter))=0, null(), 'Q' & Trade_Calendar_Quarter)

Re: Question on List box expression

Tue, 24 Jun 2014 21:15:19 GMT

I'm afraid I've tried that, and it doesn't work.

Re: Question on List box expression

venkatg6759 — Tue, 24 Jun 2014 21:16:43 GMT

Try to follow Oswaldo reply that might work .

Re: Question on List box expression

Tue, 24 Jun 2014 21:18:16 GMT

This does work, but it seems very strange that if there are no values in the calendar for that column other than 1, 2, 3, and 4 that it would be necessary. Why does concatenating the 'Q' seem to create a 'new' value?

Re: Question on List box expression

Tue, 24 Jun 2014 21:19:42 GMT

This would just give me 1, 2, 3, and 4. I already have that. It's the concatenation of 'Q' that is causing the problem.

Re: Question on List box expression

Tue, 24 Jun 2014 21:25:35 GMT

Hi,

I suggest extract Quatrer from a valid Date Field (no nulls) to make sure get correct data if you have nulls, specify a number like a default value ( if(isnull(MyField),1,MyField) ) and use it in your formula.

Something like this:

Ceil(Month( if(isnull(MyField),1,DateField) )/3) AS Quarter,

Q & '-' &if(isnull(QuarterField),1,MyField) as Quarter

Best regards.

Re: Question on List box expression

Tue, 24 Jun 2014 21:36:41 GMT

The second expression works. However, so does just this:

'Q-' & Trade_Calendar_Quarter as Quarter

Why does this work in the load script but not in the expression on the list box? Why does the list box seem to introduce a null/empty value that isn't really there?

Re: Question on List box expression

nilo_pontes — Wed, 25 Jun 2014 01:23:27 GMT

Blair, try the Dual function.

i.e.: Dual('Q' & Trade_Calendar_Quarter,Trade_Calendar_Quarter)

Best regards

Re: Question on List box expression

jagan — Wed, 25 Jun 2014 01:51:04 GMT

Hi,

I think you have null values or not a valid date values in the column Trade_Calendar_Quarter. I think it is better to create a new field instead of in frontend as calculated dimension

LOAD

If(Len(Trade_Calendar_Quarter) > 0 AND IsNum(Trade_Calendar_Quarter) <> 0, 'Q' & Trade_Calendar_Quarter) AS Quarter

FROM DataSource;

If you want the same in expression then try like this

=if(len(trim(Trade_Calendar_Quarter))>0 AND IsNum(Trade_Calendar_Quarter) <> 0, 'Q' & Trade_Calendar_Quarter)

Hope this helps you.

Regards,

Jagan.

Re: Question on List box expression

Wed, 25 Jun 2014 16:44:03 GMT

I'm going to call this answered as it does work. But I am still confused about why the expression

Q-' & Trade_Calendar_Quarter as Quarter

works in the load script but

Q-' & Trade_Calendar_Quarter

introduces the blank Q. And just using Trade_Calendar_Quarter in the list produces just 1 through 4.

There are NO nulls, invalid entries, etc in the table. This is coming from a standard calendar table with no other tables in the app. Sorry to be so frustrated, but there are times when QlikView is a total mystery to me.

Re: Question on List box expression

Wed, 25 Jun 2014 16:55:45 GMT

I created a VERY simple qvw with this data:

Calendar:

load * Inline [

Date, Quarter

1/1/14, 1

4/1/14, 2

7/1/14, 3

10/1/14, 4

];

The list box with the column Quarter shows 1, 2, 3, 4

A list box with ='Q' & Quarter as the expression shows Q1, Q2, Q3, Q4, and Q

This is what I don't get. I can code around it, I can make it work, but why should I have to?

Re: Question on List box expression

nilo_pontes — Wed, 25 Jun 2014 17:32:49 GMT

Blair,

use the Dual function on the list box expression, i.e.:

=Dual('Q' & Quarter,Quarter)

Hope it helps you.

Best regards

Re: Question on List box expression

Wed, 25 Jun 2014 20:22:42 GMT

Dual is a very useful function! It is what I will use moving forward. I guess I'm just used to concatenating using a str() function. In other environments, it would throw an error if you forgot to type the numeric to a string. I guess in QlikView it allows it but gives strange results. I am going to utilize Dual more often.

Re: Question on List box expression

nilo_pontes — Wed, 25 Jun 2014 22:24:40 GMT

Dual is very useful for many proposes, I use it many times to add fancy labels on charts.