how to detect and create combinations??

[kadf88]

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.

[diamondabhi]

I from what I understand , you can use substring and check for 'd' in the constarint and insert the rows. If this is not u what you were expecting to do then please post more info.

[kadf88]

yeah, that wasn't really what I was expecting. Let me try to clear it up:

I need to somehow generate all those rows that will have 'd' in the combination and insert them.

[ray.wurlod]

Unless you can specify the rules under which 'd' will need to be generated we won't be able to be much help.

[kadf88]

Unless you can specify the rules under which 'd' will need to be generated we won't be able to be much help. ...

well, 'd' just has to be paired up with every other element already in the table

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

[ray.wurlod]

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

Code: Select all

select max(pk_id)+1, combination_number, 'd' from table group by combination_number;

and not need DataStage at all.

To do it in DataStage, use an Aggregator stage followed by a Transformer stage.

[kadf88]

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 tha ...

yeah, that would be correct english algorithm, how do i implement it?

[ray.wurlod]

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.

[kadf88]

Ray,

You've always helped me through my various posts. I will sign up.

[kadf88]

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!

[ray.wurlod]

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.

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;

Does that fulfil your requirement?
If "they" insist on it being done "in DataStage", simply have DataStage generate that SQL!

[sb_akarmarkar]

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

[kadf88]

Hmm, ok, i'll give that a shot guys, thanks for all the help. I'll post back here later

[kadf88]

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

[kcbland]

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?