topic Re: Setting up external variable for chart expression in QlikView

Setting up external variable for chart expression

sayadutt — Sun, 07 Jun 2015 08:07:44 GMT

Hi Team,

Currently I have an expression in straight chart like :

if([Practice Area] = 'XXX' and $(vDateVar) > 5, RGB(245, 54, 83),

if([Practice Area] = 'YYY' and $(vDateVar) > 3,RGB(245, 54, 83),

red()))

Now my requirement is to get rid of the hard coded values (5 & 3 - marked in red), and read it from an external file, may be csv.

My csv will look like this.

[Practice Area], value

XXX, 3

YYY, 2

So the qvw should read this csv, pick up the corresponding value based on Practice Area and create the expression.

In this way if I modify the values in csv the same will be reflected in qvw without touching the chart expression.

Can you please guide me how I can read the values from an external file and use it in expression.

Thanks

Re: Setting up external variable for chart expression

kaushiknsolanki — Sun, 07 Jun 2015 08:10:10 GMT

Hi,

Try this.

if([Practice Area] = 'XXX' and $(vDateVar) > only({<[Practice Area] = {"XXX"}>}Value), RGB(245, 54, 83),

if([Practice Area] = 'YYY' and $(vDateVar) > only({<[Practice Area] = {"YYY"}>}Value),RGB(245, 54, 83),

red()))

Regards,

Kaushik Solanki

Re: Setting up external variable for chart expression

sayadutt — Sun, 07 Jun 2015 08:16:01 GMT

Hello Kaushik,

Did not get this.

From where are we reading the values from external file in your example?

don't we need to read those values first, store it somewhere and then use it in expression?

Thanks

Re: Setting up external variable for chart expression

kaushiknsolanki — Sun, 07 Jun 2015 08:30:50 GMT

Hi,

You must have loaded that table in script?

If not then you need to load that data in Application.

Regards,

Kaushik Solanki

Re: Setting up external variable for chart expression

sayadutt — Sun, 07 Jun 2015 09:57:04 GMT

Hi Kaushik,

I tried, but it seems not working. I cant see the RGB colors at all.Its not even going to the else part.

csv file:

Practice Area,Threshold

XXX,2

YYY,3

Load statement:

LOAD [Practice Area],

Threshold

FROM

[path]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Expression in Background Color section:

if([Practice Area] = 'XXX' and $(vDateVar) > only({<[Practice Area] = {"XXX"}>}Threshold), RGB(245, 54, 83),

if([Practice Area] = 'YYY' and $(vDateVar) > (only({<[Practice Area] = {"YYY"}>}Threshold)),RGB(245, 54, 83),

RGB(0,0,0)))

it returns white colour (default colour), its not even returning the else part.

Is the above expression correct? Is it actually going inside the if part at all?

Please suggest.

Thanks

Re: Setting up external variable for chart expression

kaushiknsolanki — Sun, 07 Jun 2015 10:12:09 GMT

Please share a sample file

Regards,

Kaushik Solanki

Re: Setting up external variable for chart expression

Anonymous — Sun, 07 Jun 2015 10:43:37 GMT

Could you share the script where you load the value into your variable vDateVar ?

For testing what happens if you hard code the value thus replacing $(vDateVar) ?

Re: Setting up external variable for chart expression

sayadutt — Sun, 07 Jun 2015 10:57:36 GMT

Hi Kaushik,

I have tried to explain my complete requirement in the edit script section. Please have a look.

Attaching the sample.

Re: Setting up external variable for chart expression

kaushiknsolanki — Sun, 07 Jun 2015 11:08:15 GMT

HI,

Here the problem was not expression, but it was of the variable you had created.

I have made changes to script.

Have a look at the attached application and give your feedback.

Regards,

Kaushik Solanki

Re: Setting up external variable for chart expression

sayadutt — Sun, 07 Jun 2015 11:28:18 GMT

Hi Kaushik,

Yes, the background colour portion is now working as expected.

Can you please suggest on the 2nd part I mentioned in the application.

// So, XXX 123 practice area & application, the day difference is 2 days and Threshold is 3 days , so it will be Amber (within limit)

// but for XXX 456 the threshold is 1 day ( it falls under 2 row of 2nd load statement. XXX-123 threshold is 3 days . For XXX-All(anything other than 123), threshold is 1 day

//The reason for having All is.... threshold for 1 application is different from others belonging to the same Practice Area

// XXX, 123, 3

// XXX, All, 1

// So the color will be Red. Date difference is coming as 5 days

There can be different threshold values for the same Practice Area (but different Applications). How to handle that?

For example, in the 1st Load statement

XXX has 3 applications 123,456,789

Now the threshold value for 123 is 3 days, but for remaining All (456&789) its 1 day.

How to handle that in the expression?

Thanks