Regarding Conditional expression

[qliksus]

Hi, I have Budget excel file which contains Year,ID,Month,Name and Value. Column ID is having values 1,2,3,4,5 and Column Name is having Actual, Budget1, Budget2, Budget3, Budget4 respectively. Now i want to compare Actual vs Budget monthwise. Where ever names are actual sum(value) define the Actual , Budget come from out of any Budget1,Budget2,Budget3,Budget4. Budget1 is for all 12 Month i.e April to March(In indian Fiancial Year), Budget2 is for 9 months(i.e. starts after the end of 1st quarter, starts from July to March), Budget3 is for 6 months(i.e. starts after the end of 2nd quarter, starts from Oct to March), Budget3 is for 3 months(i.e. starts after the end of 3rd quarter, starts from Januaryt to March). Just see the folowing data, Year ID Name Month Value 2009 1 Actual Apr 5000 2009 1 Actual May 6000 2009 1 Actual Jun 7000 2009 1 Actual Jul 8000 2009 1 Actual Aug 9000 2009 1 Actual Sep 10000 2009 1 Actual Oct 11000 2009 1 Actual Nov 12000 2009 1 Actual Dec 13000 2009 1 Actual Jan 14000 2009 1 Actual Feb 15000 2009 1 Actual Mar 16000 2009 2 Budget1 Apr 5500 2009 2 Budget1 May 6500 2009 2 Budget1 Jun 7500 2009 2 Budget1 Jul 8500 2009 2 Budget1 Aug 9500 2009 2 Budget1 Sep 10500 2009 2 Budget1 Oct 11500 2009 2 Budget1 Nov 12500 2009 2 Budget1 Dec 13500 2009 2 Budget1 Jan 14500 2009 2 Budget1 Feb 15500 2009 2 Budget1 Mar 16500 2009 3 Budget2 Jul 9000 2009 3 Budget2 Aug 10000 2009 3 Budget2 Sep 11000 2009 3 Budget2 Oct 12000 2009 3 Budget2 Nov 13000 2009 3 Budget2 Dec 14000 2009 3 Budget2 Jan 15000 2009 3 Budget2 Feb 16000 2009 3 Budget2 Mar 17000 2009 4 Budget3 Oct 12500 2009 4 Budget3 Nov 13500 2009 4 Budget3 Dec 14500 2009 4 Budget3 Jan 15500 2009 4 Budget3 Feb 16500 2009 4 Budget3 Mar 17500 2009 5 Budget4 Jan 16000 2009 5 Budget4 Feb 17000 2009 5 Budget4 Mar 18000 2010 1 Actual Apr 6000 2010 1 Actual May 7000 2010 1 Actual Jun 8000 2010 1 Actual Jul 9000 2010 1 Actual Aug 10000 2010 1 Actual Sep 11000 2010 1 Actual Oct 12000 2010 1 Actual Nov 13000 2010 1 Actual Dec 14000 2010 1 Actual Jan 15000 2010 1 Actual Feb 16000 2010 1 Actual Mar 17000 2010 2 Budget1 Apr 6500 2010 2 Budget1 May 7500 2010 2 Budget1 Jun 8500 2010 2 Budget1 Jul 9500 2010 2 Budget1 Aug 10500 2010 2 Budget1 Sep 11500 2010 2 Budget1 Oct 12500 2010 2 Budget1 Nov 13500 2010 2 Budget1 Dec 14500 2010 2 Budget1 Jan 15500 2010 2 Budget1 Feb 16500 2010 2 Budget1 Mar 17500 2010 3 Budget2 Jul 2010 3 Budget2 Aug 2010 3 Budget2 Sep 2010 3 Budget2 Oct 2010 3 Budget2 Nov 2010 3 Budget2 Dec 2010 3 Budget2 Jan 2010 3 Budget2 Feb 2010 3 Budget2 Mar 2010 4 Budget3 Oct 2010 4 Budget3 Nov 2010 4 Budget3 Dec 2010 4 Budget3 Jan 2010 4 Budget3 Feb 2010 4 Budget3 Mar 2010 5 Budget4 Jan 2010 5 Budget4 Feb 2010 5 Budget4 March Budget is latest value out of the 4 Budget subvalues. But conditions are 1> For Apr to Jun it is Budget1,July to Sep it is Budget2, Oct to Dec it is Budget3 and rest Budget4. 2> If there is no value for a particular budgetname then it has to take the previous budgetname value. Like just see for 2010 year there no data for Oct Month. According to rule-1 value is the Budget3 value. But it has no value that's why it will find the value is exist for Budget2 or not. If not then it will find the value for Bufget1. Since there is value for Budget1 it will show the value-12500. But for Year-2009, for Month October all Budget1,Budget2,Budget3 have values. So for 2009 it will show the value of Budget3 i.e 12000. So can anyone help me how to write the Conditional Expression whic will satisfy the condition-1 and conditio-2?????

[#2]

You need to structure the excel database differently, see attached.

You can then load the script as follows

LOAD Year,

Month,

Actual,

Budget1,

Budget2,

Budget3,

Budget4,

if(Budget4 <> 0, Budget4, if(Budget3<>0,Budget3,if(Budget2<>0,Budget2,Budget1))) as Budget

FROM (biff, embedded labels, table is Sheet1$);

For the chart or report the expression will be

=Sum(Actual- Budget)

[#3]

If you need to keep the excel database in it's existing format then you use the following

Table_Temp:

LOAD Year,

ID,

Name,

Month,

Value

FROM (biff, embedded labels, table is Sheet1$);

Table:

NOCONCATENATE LOAD Year,

Month,

ID,

sum(if(Name like '*Actual*',Value)) as Actual_Val,

sum(if(Name like '*Budget1*',Value)) as Budget1_Val,

sum(if(Name like '*Budget2*',Value)) as Budget2_Val,

sum(if(Name like '*Budget3*',Value)) as Budget3_Val,

sum(if(Name like '*Budget4*',Value)) as Budget4_Val

RESIDENT Table_Temp

Group By Year, Month, ID;

DROP TABLE Table_Temp;

The expression in the chart or the report will be

=if(Budget4 <> 0, Budget4, if(Budget3<>0,Budget3,if(Budget2<>0,Budget2,Budget1)))

[qliksus]

Thanks for your reply. But i have also done by using above type method. But this is just a hardcode method. Because if there are 10 Budget instead of 4 Budget then it will become a very big conditional expression. This is where i am facing problem.

[prieper]

Hi,

might then make sense, if you have a look into the CROSSTABLE-functionality of QlikView.

Your may give it a try with the below script:

Budget:
CROSSTABLE(Item, Value, 2)
LOAD
*
FROM
[.\DB1.xls]
(biff, embedded labels, table is Sheet1$)
WHERE
Year <> 'Total';

HTH

Peter