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
logic help
Moderators: chulett, rschirm, roy
Re: logic help
Just do a constraint on the Transformer based on your need to filter out.....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
RK
Re: logic help
[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
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
Re: logic help
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.
Hope this will help.
Kris~
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.
Then extract the data with below SQL.
CREATE TABLE TABLE_XYZ
( claim_id NUMBER(2),
adj_id varchar2(2)
);
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~
~Kris
Re: logic help
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~
kris~
~Kris
-
- Participant
- Posts: 27
- Joined: Wed Jan 03, 2007 1:29 am
Re: logic help
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" .
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" .
Hemachandra
Code: Select all
incoming rows --> Copy --> Aggregator (group key claim_id,Aggregation = Sum)
| |
| |
| |
| |
| Filter (On Sum = 01)
| |
| |
| Merge------------------------>output
| ^
| update link |
|--------------------|