stuck with some Business scenario

devesh_ssingh · Post by **devesh_ssingh** » Thu Oct 25, 2012 11:42 am

We have input;

Cust_Id Policy System Role

1 a b 010
1 a b 020
1 a b 230

2 c d 010
2 c d 020
2 c d 030

3 e f 010
3 e f 230

4 g h 230

We want to take through 020 and 030 as standard so no worries about them.

If there are roles 010 & 230, we only want to take 230.

If we have 010 without a 230, we still want to take through the 010.

Any thoughts? I have highlighted (in bold) the ones we want to retain and the ones in not bold need to be dropped.

Cheers
Devesh

ray.wurlod · Post by **ray.wurlod** » Thu Oct 25, 2012 4:17 pm

What have you designed/tried and how did what you tried fail to meet your expectations? Are you using a version with looping available in the Transformer stage?

devesh_ssingh · Post by **devesh_ssingh** » Fri Oct 26, 2012 5:56 am

Hi Ray,

i am using 8.1 version so no looping there.

i have tried using x'mer by holding previous column value but i need to select between role 010 and 230. there are other column value are also which is not satisfying.

please suggest

bhasds · Post by **bhasds** » Fri Oct 26, 2012 9:51 am

Hi devesh,

You may try the below steps-

1.With the help of filter stage split the records into two stream.

a) Having Role as 20 and 30.
b) Having Role as 10 and 230.

2.Map both the link to a funnel stage.

Sort the link carrying Role as 10 and 230 in decending order taking Cust_Id Policy System as key column and select unique.

The above should get you the desired output. Please let me know if you have any issue.

priyadarshikunal · Post by **priyadarshikunal** » Fri Oct 26, 2012 10:15 am

just use the good old way, assign a rank using stage variable.

020 and 030 can be sent to one link

on other one sort the data desc order of role.

not assign numbering on the records so that 230 is assigned rank 1 and 010 is assigned rank 2 and rank 1 if 230 is not available and just filter on rank 1.

bhasds · Post by **bhasds** » Fri Oct 26, 2012 1:09 pm

Hi Devesh,

Even you may try this also with a single transformer-

1.Sort the records in the descending order based on the in transformer.

2. In stage variable-

Code: Select all

If Cust_Id <> SV2 Then Cust_Id :" ": Policy:" ": System:" ": Role Else SV1 :" ": Role      SV1
Cust_Id     SV2

3.In derivation constraint-

Code: Select all

Field(SV1," ", Dcount(SV1," ")) ="230" Or Field(SV1," ", Dcount(SV1," ")) ="30" Or 
Field(SV1," ", Dcount(SV1," ")) ="20" Or (Field(SV1," ", Dcount(SV1," ")) ="10" And 
Index(SV1,"230",1) ="0")