Folks, I need your help ...a newbie to informatica

Here is my scenario. I have a source table with 5 columns date, opening bal, prod qty, sold qty and closing bal. Opening bal and closing bal are blank in the source table. My question is can opening bal and closing be populated in the target using mapping variable without going through staging?

closing bal=opening bal + prod qty - sold qty

and opening bal = closing bal of previous day ( assume opening bal of first day is 0).


I tried the following steps


1) Defined a mapping variable $$open_bal (initial value =0)
2) Created an expression transformation

opening_bal=$$open_bal (Input unchecked, output checked)
date - source input
prod_qty - source input
sold_qty - source input
v_closing_bal - $$open_bal+prod_qty - sold_qty (variable checked, input & output unhecked)
closing_bal=v_closing_bal (output)
v_open_bal=SETVARIABLE($$BEGIN_BAL, v_closing_bal)

3) projected ports opening_bal,date,prod_qty,sold_qty & closing_bal to target table.


It doesn't look like server evaluates the current value of a variable as each row passes through the mapping. But the PowerCenter Server saves the latest value of a mapping variable to the repository at the end of each successful session.


Regards,
Robert

Hi,

No its not possible because a mapping variable is used only when the value is common for all the rows. But, in this case open_bal is not same for all the record and at the end of session run mapping variable will be populated with close_bal of last record.

This value of mapping variable will be used for open_bal of first record which is not correct.

May be m going with wrong scenario, can u explain it in detail?