How to compare in script level ??

[qv_testing]

Hi Community,

I Need help..

These are my fields, i have to compare data PostDate wise.

Key

PostDate

PostID01

PostID02

Amount

I want to calculate 2 fields.

Case1:

If Year=2014, compare with PostID01

PostDate:  31/01/2014  (Month End)

PostID01 = ACCOUNT

Sum(Amount)  as FilterData_IN

PostDate:  01/02/2014  (Month Start)

PostID01 = ACCOUNT1

Sum(Amount)  as FilterData_OUT

Case2:

If Year=2015, compare with PostID02

PostDate:  31/01/2015  (Month End)

PostID02 = SUB06

Sum(Amount)  as FilterData_IN

PostDate:  01/02/2015  (Month Start)

PostID02 = 2015

Sum(Amount)  as FilterData_OUT

Output Should be  

Calculated Field	2014	2015
Post_IN	$ 1,984,40	$ 2,539,412.90
Post_Out	$ 2,365,559.11	$ 1,718,012.59

Thanks in Advance..

PFA,

[swuehl]

Maybe add something along these lines to your script:

RESULT:

LOAD YEAR, FILTER,

  Sum(Amount) as SUM

WHERE Len(Trim(FILTER))

GROUP BY YEAR, FILTER;

LOAD *,

  Year(PostDate) as YEAR,

  If( ((Year(PostDate)=2014 and PostID01 = 'ACCOUNT') or (Year(PostDate)=2015 and PostID02 = 'SUB06')) and PostDate = Floor(MonthEnd(PostDate)), 'FilterData_IN'

  , If(((Year(PostDate)=2014 and PostID01 = 'ACCOUNT1') or (Year(PostDate)=2015 and PostID02 = '2015')) and PostDate = Floor(Monthstart(PostDate)), 'FilterData_OUT')) as FILTER

Resident INPUT;           //I've labelled your INLINE table INPUT

FILTER	YEAR	2014	2015
FilterData_IN		198440,00	2539412,89
FilterData_OUT		2365559,10	1718012,60