I have a source table like :
I want target table like this :
i tried expression transformation but not able to load data in the target table. Can someone help to find the solution
In expression transformtation :
Create an variable v_name and check this expression
v_name= IIF( NOT ISNULL(Empno), name , v_name || name
out_empno = IIF(IS NULL (Empno) ,v_empno) , in_Empno
out_name = v_name.
out_flag = IIF(IS NULL (Empno) ,'Y','N')
v_empno = in_Empno.
use flag = 'Y' in the group.
I think this will help you.
Algo. for your Scenario:
Step-1: Hold the Previous Record's ID (first Column date) in an Expression variable.
Step-2: When getting the Current Record, check for the NULL value in it. If, the current record has NULL Value for ID field, UPDATE the CURRENT RECORD value with PREVIOUS RECORD ID value. This also must happen in another EXPRESSION VARIABLE.
Step-3: Now, you are successfully holding ID value for all the records (By assigning its corresponding previous record's ID Value)
Step-4: Current Dataset (flowing out of expression) is in NORMALIZED format.
Step-5: This Dataset needs to be DENORMALIZED to get the required TARGET data. (This can be done in a traditional approach of EXPRESSION --> AGGREGATOR --> TARGET)