topic Best way to set one value based on another in QlikView

Best way to set one value based on another

Thu, 24 May 2012 16:03:46 GMT

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.

Best way to set one value based on another

swuehl — Thu, 24 May 2012 16:16:03 GMT

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

Best way to set one value based on another

Fri, 25 May 2012 08:58:02 GMT

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?

Best way to set one value based on another

swuehl — Fri, 25 May 2012 09:01:47 GMT

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

Best way to set one value based on another

Fri, 25 May 2012 09:12:08 GMT

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

Best way to set one value based on another

swuehl — Fri, 25 May 2012 10:52:38 GMT

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;

Best way to set one value based on another

rohit214 — Fri, 25 May 2012 11:01:38 GMT

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

Best way to set one value based on another

Fri, 25 May 2012 13:03:09 GMT

Thanks for your help Stefan - really appreciate it.