generate distinct combinations for a given set of data

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Nsg
Premium Member
Premium Member
Posts: 37
Joined: Thu Jan 26, 2006 1:21 pm

generate distinct combinations for a given set of data

Post by Nsg »

Hello,

I need to create a bridge table with all possible combinations for a clms and want to know if it can be done through DS 8.7 version -

Eg -


Dimension table
------------------
Code_clm
1
2
3

Bridge table
--------------
bridge_key.....Code_clm
0001 .......... 1
0002 .......... 2
0003 .......... 3
0004 .......... 1
0004 .......... 2
0005 .......... 1
0005 .......... 3
0006 .......... 2
0006 .......... 3
0007 .......... 1
0007 .......... 2
0007 .......... 3

Note : the "......" are just for formating.

Please help if you know of a way to achieve this in DataStage 8.7.

Thanks,
nsg
ankursaxena.2003
Participant
Posts: 96
Joined: Mon May 14, 2012 1:30 pm

Post by ankursaxena.2003 »

What will be the output if the bridge_key has more than 3 values. For example, it has 5 8's. Then what will be the output.

And in you example when bridge_key changes from 4 to 5 shouldn't it be 3 instead of 1.
Nsg
Premium Member
Premium Member
Posts: 37
Joined: Thu Jan 26, 2006 1:21 pm

Post by Nsg »

The bridge table has to have all the possible unique combination. The length for the bridge_key is number(10) for possible unique combinations for 28 values in the code table.

eg - if the code table has 3 values (1,2,3) then the code should generate 7 distinct combinations in the bridge table -
The bridge table can be express in this way as well -

Bridge table
----------------
bridge_key..... Code_clm (values)
0001 ................ 1
0002 ................ 2
0003 ................ 3
0004 ................ 1,2
0005 ................ 1,3
0006 ................ 2,3
0007 ................ 1,2,3

Thanks,
nsg
Post Reply