DSXchange

hi,
my input columns:-
claim_id adj_id
1 00
1 01
1 02
2 00
2 00
3 00
3 01
3 01
4 00
4 02
5 00
5 00
5 01
o/p
claim_id adj_id

3 00
3 01
3 01
5 00
5 00
5 01

my ouput shuld have only those rows where for a particular claim_id has combination of x number of 00 and y number of 01 i.e i dnt want those rows whose claim_id has"02" or just"00'. i need rows whose claim_id is combination of both "00' and "01'
the datatype are varchar(5)

plz help

pattemk wrote:hi,
my input columns:-
claim_id adj_id
1 00
1 01
1 02
2 00
2 00
3 00
3 01
3 01
4 00
4 02
5 00
5 00
5 01
o/p
claim_id adj_id

3 00
3 01
3 01
5 00
5 00
5 01

my ouput shuld have only those rows where for a particular claim_id has combination of x number of 00 and y number of 01 i.e i dnt want those rows whose claim_id has"02" or just"00'. i need rows whose claim_id is combination of both "00' and "01'
the datatype are varchar(5)

plz help

Just do a constraint on the Transformer based on your need to filter out.....

[quote="g_rkrish"][quote="pattemk"]hi,
my input columns:-
claim_id adj_id
1 00
1 01
1 02
2 00
2 00
3 00
3 01
3 01
4 00
4 02
5 00
5 00
5 01
o/p
claim_id adj_id

3 00
3 01
3 01
5 00
5 00
5 01

my ouput shuld have only those rows where for a particular claim_id has combination of x number of 00 and y number of 01 i.e i dnt want those rows whose claim_id has"02" or just"00'. i need rows whose claim_id is combination of both "00' and "01'
the datatype are varchar(5)

plz help[/quote]

Just do a constraint on the Transformer based on your need to filter out.....[/quote]

thanks for replyin
i cant figure out how to what logic to write, if i write adj<> 02 then i also get the these rows in my out put which i dnt want

claim_id adj_type
1 00
1 01
2 00
2 00
4 00

plz help me with logic

thank you

Hey,

Sometimes you can take advantage of Database for an ETL like this.

Very easy to do this in Database compared to do this kind of job in a 3-pass file processing.

Create a table and load your input data.

CREATE TABLE TABLE_XYZ
( claim_id NUMBER(2),
adj_id varchar2(2)
);

Then extract the data with below SQL.

select T3.CLAIM_ID,T3.ADJ_ID from TABLE_XYZ T3,
(select distinct CLAIM_ID,ADJ_ID from TABLE_XYZ where (CLAIM_ID) not in (select T1.CLAIM_ID from
(select distinct CLAIM_ID,ADJ_ID from TABLE_XYZ where ADJ_ID = '02') T1) and ADJ_ID = '01' ) T2
where T3.CLAIM_ID = T2.CLAIM_ID;

Hope this will help.

Kris~

Forgot to say that this SQL is written only based on your input data. You should tweak the SQL if your actual data has more cases or the cases which are not covered in your input data.

kris~

I have a solution but it will in terms of server job.
Change it to have it in terms of Px

Create a dummy hash file somewhere.

In this job have the hash file as lookup as well as update stage. The hash file will have claim_id as key and 3 extra columns like

VAL_00_FG
VAL_01_FG
VAL_OTH_FG

Update the value of _FG with "Y" for corresponding ADJ_ID.

For the other ADJ_ID _FG set the value from hash file lookup

So the hash file will have like

CLAIM_ID VAL_00_FG VAL_01_FG VAL_OTH_FG

1 Y Y Y
2 Y blank blank
3 Y Y blank
4 Y blank Y
5 Y Y blank


So in other job have the input to lookup this hash file and pass the recirds for which the key has "Y" for (VAL_00_FG & VAL_00_FG) and VAL_OTH_FG<> "Y" .

God I dont know how to align the design above. Anyway .. just put a copy stage for the incoming set of rows .. send it to an aggregator stage where group key = claim_id .. aggregation type = calculation .. sum(adj_id) .. put a filter stage from the output of the aggregator stage. . filter out only those rows whose sum = 01 .. Use the output link of filter stage as a master link to a merge whose update link is the other link from the Copy stage set at first. ..