Can we pass records from update startegy to expression & then to other 1

[srvu]

I have two tables T1 and T2
T2 is having amt fields where on joining two tables i get set of records(cartesian records).
I should add amts of those cartesian records or records with same PK and get one single record for set of records.
(i have checked that condition in EXP and flag rows to update strategy to update to add amts & get one record per set of records)
Now, i should pass that to lkp to join PK with LKP.PK to check same condition i.e now i should add LKP.amt fields of LKP if there are same set of PK as a result of join with T1.

Now i can again do that with exp and should use update strategy2 again.

so, can i use UPstrategy1 to pass PK1 to LKP.T3 to join and get records which will again pass to exp.

 

[blom0344]

Let's start with the first 2 lines in your post.
Why are you creating a cartesion product to begin with?

The other lines in your post are very unclear. Are you trying to create a distinct set out of the cartesian set again?

Ties Blom

 

[srvu]

I have T1 and T2
T2 have enrollid and some amt fields,if T2 is having multiple records with same enrollid then add amts and get a single record.
After this join to T1

Now,after this table T3 is also having enrollid and some amt fields,if there are multiple records with same enrollid then add all amts and get single record and now join that to T2

 

[blom0344]

No need for a lookup then. Simply aggregate where you need to consolidate to one record for an enrollid and use joiner transformations. The key is to designate one table as the master and use outer join strategy to add data from the other tables.

Ties Blom

 

[srvu]

Output one record meant that one record per member

If for each member there are 2 records then add those amts from two records and drop prev,current should contain amt of current record and prev record

Enrollid accumid accumvalue

1122 C1167 100

1122 C1169 0

1123 C77414 20

1123 C77414 10 In these two records enrollid and accumid are equal

O/P should be
1122 C1167 100
1122 C1169 0
1123 C77414 30

After adding amts with same enrollid and accumid we should get single record.
later join that with T1 table

 

[blom0344]

You can use an aggregate transformation with group by on the ports EnrollID en Accumid which will get you the results you are after in your example.
However, are you familiar with the aggregate transformation at all? You will have to think in set-based terms, not in previous/current (ETL <> programming)

Ties Blom

 

[srvu]

Hi Blom

Thanks a lot thats working

But had some problem in display of amt fields i.e.
From source i am getting length of amt as 18 and target length is 9.

In agg amt is coming this way 79.47000000000.
i have used Round function to round but as target is of length 9,when used round function no value is displayed in target including zero....

Can you please assist me

 

[blom0344]

For a numeric field there is no concept of length. Scale and precision are the definition of a numeric field. You need to look into the definition of the target how these are set.
Depending on the tool you use to query the target it may or may not show the trailing zero's

Ties Blom