how to detect and create combinations??
Moderators: chulett, rschirm, roy
how to detect and create combinations??
I have the following problem, its kind of complicated but here goes:
I have a table that holds different combinations like so:
pk_id combination_number elements
1 1 a
2 1 b
3 2 a
4 2 c
5 3 b
6 3 c
each combination consists of 2 elements.
now I have a new element, say 'd', I need to add all the new rows with combinations involving 'd'
can anyone explain how i can do this?
thanks, any help is appreciated.
I have a table that holds different combinations like so:
pk_id combination_number elements
1 1 a
2 1 b
3 2 a
4 2 c
5 3 b
6 3 c
each combination consists of 2 elements.
now I have a new element, say 'd', I need to add all the new rows with combinations involving 'd'
can anyone explain how i can do this?
thanks, any help is appreciated.
-
- Premium Member
- Posts: 108
- Joined: Sat Feb 05, 2005 6:52 pm
- Location: US
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
well, 'd' just has to be paired up with every other element already in the tableray.wurlod wrote:Unless you can specify the rules under which 'd' will need to be generated we won't be able to be much help. ...
so, for example, i'd need something like this generated:
pk_id combination_number elements
7 4 a
8 4 d
9 5 b
10 5 d
11 6 c
12 6 d
etc
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
So the algorithm, in English, is something like "for each distinct combination_number generate a new row containing a pk_id one larger than the current largest pk_id value for that combination_number and set elements to 'd'". Is that correct?
If the source is a table you could and not need DataStage at all.
To do it in DataStage, use an Aggregator stage followed by a Transformer stage.
If the source is a table you could
Code: Select all
select max(pk_id)+1, combination_number, 'd' from table group by combination_number;
To do it in DataStage, use an Aggregator stage followed by a Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can implement as described in the remainder of my post.
To view the full content of premium posts, sign up for premium membership. It's less than $1 per week, and the revenue is used to fund this site's continued existence.
To view the full content of premium posts, sign up for premium membership. It's less than $1 per week, and the revenue is used to fund this site's continued existence.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Ray,
Hmm ok, I just read your implementation; it's not quite what I needed.
The combination_number is just an indictator. I'll try to explain better:
I have a table with the following rows
id,element
1, a
2, b
3, c
4, d
I need to, in DS, make a table with rows that contain every possible two element combination. something like this:
id,element1,element2
1,a,b
2,a,c
3,a,d
4,b,c
5,b,d
6,c,d
order doesn't matter (eg. element1=a and element2=b is the same as element2=a and element1=b)
thanks again!
Hmm ok, I just read your implementation; it's not quite what I needed.
The combination_number is just an indictator. I'll try to explain better:
I have a table with the following rows
id,element
1, a
2, b
3, c
4, d
I need to, in DS, make a table with rows that contain every possible two element combination. something like this:
id,element1,element2
1,a,b
2,a,c
3,a,d
4,b,c
5,b,d
6,c,d
order doesn't matter (eg. element1=a and element2=b is the same as element2=a and element1=b)
thanks again!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Again, probably easiest done with SQL rather than with DataStage. Form a join of the table to itself, based on id being the same and element being different.
Does that fulfil your requirement?
If "they" insist on it being done "in DataStage", simply have DataStage generate that SQL!
Code: Select all
SELECT t1.ID, t1.ELEMENT, t2.ELEMENT
FROM tablename t1, tablename t2
WHERE t1.ID = t2.ID
AND t1.ELEMENT <> t2.ELEMENT;
If "they" insist on it being done "in DataStage", simply have DataStage generate that SQL!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Hi,
Suppose your table name is DUM
use following query to get data from source
SELECT DISTINCT A.ELEMENT AS ELEMENT1,B.ELEMENT AS ELEMENT2 from DUM A CROSS JOIN DUM B WHERE A.ELEMENT<>B.ELEMENT
after that u need to apply some lookup logic in ETL:
lookup should be dynamic
like ELEMENT1 will go into hash file :
and ELEMENT2 from source map with hash file ELEMENT1
get in constraint say ELEMENT1 from source not equal to ELEMENT2....
I hope u will get your result
Thanks,
Anupam
akarmarkar@smart-bridge.co.in
Suppose your table name is DUM
use following query to get data from source
SELECT DISTINCT A.ELEMENT AS ELEMENT1,B.ELEMENT AS ELEMENT2 from DUM A CROSS JOIN DUM B WHERE A.ELEMENT<>B.ELEMENT
after that u need to apply some lookup logic in ETL:
lookup should be dynamic
like ELEMENT1 will go into hash file :
and ELEMENT2 from source map with hash file ELEMENT1
get in constraint say ELEMENT1 from source not equal to ELEMENT2....
I hope u will get your result
Thanks,
Anupam
akarmarkar@smart-bridge.co.in
ok guys, thanks for all the help so far but it seems like I gave you guys the wrong info. i actually need to do the following:
ok, so i have my original table with the following structure:
id, element
1, a
2, b
3, c
4, d
From that, I need to create a table that can have every single possible combination of the above elements, like so:
id, combo_num, element
1, 1, a
2, 2, b
3, 3, c
4, 4, d
5, 5, a
6, 5, b
7, 6, a
8, 6, b
9, 6, c
etc.
basically, the above table reads like so:
combo 1: a
combo 2: b
...
combo 5: a,b
combo 6: a,b,c
etc.
so with n=4 elements, there should be (2^n)-1 combinations so in this case, 15 combinations..
can anyone help me with this?
I don't know where to start, thanks
ok, so i have my original table with the following structure:
id, element
1, a
2, b
3, c
4, d
From that, I need to create a table that can have every single possible combination of the above elements, like so:
id, combo_num, element
1, 1, a
2, 2, b
3, 3, c
4, 4, d
5, 5, a
6, 5, b
7, 6, a
8, 6, b
9, 6, c
etc.
basically, the above table reads like so:
combo 1: a
combo 2: b
...
combo 5: a,b
combo 6: a,b,c
etc.
so with n=4 elements, there should be (2^n)-1 combinations so in this case, 15 combinations..
can anyone help me with this?
I don't know where to start, thanks
Your example is not understandable. Your first table shows id ranged from 1 to 4, but your second table shows it ranged from 1 to 9,etc. Where does combo_num come from? Why does the "cartesian" type effect occur at 5, where the multiplication is a factor of the position after 5?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle