Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
qv_testing
Specialist II
Specialist II

How to compare in script level ??

Hi Community,

I Need help..

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

KeyPostDatePostID01PostID02Amount

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 Field20142015
Post_IN$ 1,984,40$ 2,539,412.90
Post_Out$ 2,365,559.11$ 1,718,012.59


Thanks in Advance..


PFA,

1 Reply
swuehl
MVP
MVP

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 20142015
FilterData_IN 198440,002539412,89
FilterData_OUT 2365559,101718012,60