Question on List box expression

[#1]

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?

[nilo_pontes · Accepted solution]

Blair, try the Dual function.

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

Best regards

[jagan]

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.

[#4]

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.

[#5]

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?

[nilo_pontes]

Blair,

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

=Dual('Q' & Quarter,Quarter)

Hope it helps you.

Best regards

[#7]

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.

[nilo_pontes]

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