topic Re: Need Help In Expression in QlikView

Need Help In Expression

neha_shirsath — Wed, 11 Dec 2013 08:35:05 GMT

Hello Community,

I need your help for below.

I'm attaching the excel,

Date

816

820

823

In this,

Column C for Date 1st is calculate- (Value(811)+ 1st Date A value(50) - 1st date B value (45))= 816

for Date 2nd is- (Column C value for 1st Date(816)+2nd date A(52) - 2nd Date B Value(48))=820

for Date 3rd is- (Column C value for 2ndt Date(820)+3rd date A(54) - 3rd Date B Value(51))=823

and so on..

Please help how to write expression for this scenario.

I'm attaching the excel.

Thanks In Advance.

-Neha

Re: Need Help In Expression

Wed, 11 Dec 2013 08:38:25 GMT

Hi,

Use the peek() function
LOAD .... A+B+peek('C') as C ...

Fabrice

Re: Need Help In Expression

hic — Wed, 11 Dec 2013 08:42:07 GMT

Small correction:

A+B+Peek('C')

will evaluate to NULL on the first row, since Peek('C') is undefined. Use RangeSum instead:

RangeSum(A,B,Peek('C'))

HIC

Re: Need Help In Expression

neha_shirsath — Wed, 11 Dec 2013 08:54:50 GMT

Thanks For you valuable reply,

But I can't do this on script level as my A & B value coming from 2 different qvd's.

This is only sample excel i have created to get an idea.

Can we do this on expression level to calculate C column?

Thanks,

Neha

Re: Need Help In Expression

qlikpahadi07 — Wed, 11 Dec 2013 09:19:38 GMT

I think you can join/concatenate the QVD and perform the same with resident which HIC has mentioned

Re: Need Help In Expression

Wed, 11 Dec 2013 09:47:55 GMT

You will have to put A and B into one single table (use JOIN).

Sort also this table because the peek('C') function returns the value of the preceding row (so you will prefer it returns the value of the previous date)

As Henric suggested, yes use Rangesum(), ifnot the NULL will propagate.

Fabrice

Re: Need Help In Expression

neha_shirsath — Wed, 11 Dec 2013 11:20:21 GMT

Hello,

Thanks to all.

HIC Post help me to find solution,

i have not join the qvd's but tried it in expression itself and it works correctly.

Below is the expression for help-

sum(Value) + rangeSum(above(sum(A),0,RowNo()))-rangeSum(above(B),0,RowNo()))

Thanks

Neha