USING PEEK FIND THE OPENING AGAINEST CLAIMNO

[#1]

Dear friends Please find the Attachment

and i want out put like below

where ever movement type -'NC' MY Opening amount is 0 other wise my closing amount as my opening amount for net record

out put

IACOD_CLM_NO	MOVEMENTDATE	MOVEMENT_TYPE	closing	IACOD_CLM_MVMT	CLAIM_LINK	opening
VP00264340	09/05/2011	NC	13000	1	VP002643401	0
VP00264340	27/06/2011	RC	13144	2	VP002643402	13000
VP00264340	27/06/2011	PY	7600	4	VP002643404	13144
VP00264340	27/06/2011	CC	0	5	VP002643405	5544

[miskinmaz]

Hi,

Need to add following expression within the load statement of the table

if(MOVEMENT_TYPE<>'NC',Peek(closing),0) as opening_amt

Regards,

Miskin

[swuehl]

You can do it using something along these lines:

LOAD IACOD_CLM_NO,

    MOVEMENTDATE,

    MOVEMENT_TYPE,

    closing,

    IACOD_CLM_MVMT,

    CLAIM_LINK,

    If(Peek(IACOD_CLM_NO)=IACOD_CLM_NO and MOVEMENT_TYPE <> 'NC', Peek(closing), 0) as opening

FROM

[DATA (1).xls]

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

The input records need to be sorted by claim No and movement (this is already the case in your sample excel file), since your sample shows multiple records per movement date, you probably need another field to sort your data, maybe IACOD_CLM_MVMT.