How to implement Conditional join in QlikView?

[#1]

Dear All,

I have a Table-1 with following structure:

E-Name	Month	Sales
ABC	200901	1000
ABC	200902	1200
ABC	200903	1400
ABC	200904	1500
ABC	200905	1200
ABC	200906	1100

and I need to calculate cummalative measure :

E-Name	Month	Sales	Sales - Cumm
ABC	200901	1000	1000
ABC	200902	1200	2200
ABC	200903	1400	3600
ABC	200904	1500	5100
ABC	200905	1200	6300
ABC	200906	1100	7400

We can derive the above result with self-Join Query.

Please let me know the method to proceed the same in QlikView.

Select .... from Table - 1 inner join Table-1 Table-2 on

(Table-1.E-name = Table-2.E-name and Table-1.Month <= Table-2.Month)

Thanks & Regards

Sarat

[hector]

Hi, you don't need the join, in the attached file you can check 2 ways

• with an accumulated field using rangesum + peek
• in a table using above()

check out and tell me if it solves your problem

rgds

PS. this is the easy way, cause you only have one article, with 2 or more you will need to improve the script

[#3]

Hi,

Here you are the sentence:

=Sum(Sales) + rangesum(Above(Sum([Sales]), 1, 3))

Best regards.

[#4]

Thanks for your reply,

I am getting error: "Above() function is not a valid function".

My QlikView Version -8.0.1.4760.10

My Script:

Test1:
Load * Inline
[
Ename,Month,Sales
ABC,200901,1000
ABC,200902,1200
ABC,200903,1100
ABC,200904,1500
ABC,200906,1200
];

Sales:
Load *,Sum(Sales) + rangesum(Above(Sum([Sales])), 1, 3) as Cumm_Sum
resident Test1;

Drop Table Test1;

Thanks & Regards,

Sarat

[hector]

hi, above() is a function for charts, is not recognized in the script section

the similar function in "script environment" is peek() or previous()

see the attached document (if this times can be uploaded xD)

rgds