Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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.