Best way to set one value based on another

[#1]

Hi,

I have a table that I have imported into QV containing budget information. The table has the following fields:

Objective

FY_Budget

Numerical_Forecast

The accountants have this thing they are fixated on called the Forecast_Outturn which isn't included in the budget table. It's calculated on the fly and the rule is:

If (Numerical_Forecast is NULL)

Forecast_Outturn = FY_Budget

Else

Forecast_Outturn = Numerical_Forecast

End If

I want to create a simple chart that shows the FY_Budget against the Forecast_Outturn for any given Objective Code. Can anyone suggest the best way to calculate the Forecast_Outturn value (i.e. should I do it in the load script, as an expression etc.) and make it available to my QlikView workbook?

Thanks in advance,

markp3rry.

[swuehl]

I would create the Numerical_Forecast as new field in your script:

LOAD

Objective,

FY_Budget,

Numerical_Forecast,

if(isnull(Numerical_Forecast),FY_Budget,Numerical_Forecast) as Forecast_Outturn

FROM Table;

Then create e.g a straight table chart with dimension Objective and expression

=FY_Budget / Forecast_Outturn

(or whatever you want to see)

Regards,

Stefan

[#3]

Thanks for the help Stefan - really appreciate it. Just checked the database and the Numerical_Forecast can either be NULL or 0 - how can I modify the LOAD script to accomodate this?

[swuehl]

Try

if(isnull(Numerical_Forecast) or Numerical_Forecast=0,FY_Budget,Numerical_Forecast) as Forecast_Outturn

and if Numercial_Forecast can be NULL, 0 or blank:

if(len(trim(purgechar(Numerical_Forecast,'0')))=0,FY_Budget,Numerical_Forecast) as Forecast_Outturn

[#5]

Thanks again! If each Objective has multiple rows (made up of a number of different sub budgets) then how would I SUM each set of values for each Objective in the expression?

I tried

budgets:

LOAD OBJECTIVE,

FY_BUDGET,

NUMERICAL_FORECAST,

IF(ISNULL(SUM(NUMERICAL_FORECAST)) OR SUM(NUMERICAL_FORECAST) = 0, FY_BUDGET, NUMERICAL_FORECAST) AS Forecast_Outturn;

SQL SELECT * FROM BUDGET;

but it throws an error

[swuehl · Accepted solution]

You need to use a group by clause to allow the use of an aggregation function like sum.

You could do the sum on the DB server, using the SQL syntax of this system. Maybe you need to ask someone from your IT to help you with that (it will look probably (almost) identical to below statements):

Or try

TMPbudgets:

SQL SELECT * FROM BUDGET;

budgets:

LOAD

OBJECTIVE,

sum(FY_BUDGET) as FY_BDUGET,

sum(NUMERICAL_FORECAST) as NUMERICAL_FORECAST,

IF( SUM(NUMERICAL_FORECAST) = 0, sum(FY_BUDGET), sum(NUMERICAL_FORECAST)) AS Forecast_Outturn

resident TMPbudgets group by OBJECTIVE;

drop table TMPbudgets;

[rohit214]

Hi,

try this

budget:

Load

objective,

fy_budget,

numeric_forecast,

if (isnull(fy_budget)=-1 or len(fy_budget)=0,fy_budget,numeric_forecast) as forecast_outturn

from

xyz.qvd;

thanks&regards

rohit

[#8]

Thanks for your help Stefan - really appreciate it.