Hi Experts,
My Source Table format is :
Col1,Col2
P,K
K,J
L,J
I,H
M,Q
M,O
M,J
J,H
G,C
H,F
F,C
J,D
D,A
C,A
O,E
N,E
E,A
E,B
Col1 and Col2 contains filenames and we have to find full trace of that filename in below format.
We have to start from end node or root node.
Eg. In above example root nodes are A,B,Q , as they exists in only col2 and not in col1
Expected output:
Col, Root,Branch,Seq
A,1,1,1
E,1,1,2
N,1,1,3
A,1,2,1
C,1,2,2
F,1,2,3
H,1,2,4
I,1,2,5
A,1,3,1
D,1,3,2
J,1,3,3
M,1,3,4
A,1,4,1
C,1,4,2
G,1,4,3
A,1,5,1
E,1,5,2
O,1,5,3
M,1,5,4
A,1,6,1
D,1,6,2
J,1,6,3
L,1,6,4
A,1,7,1
D,1,7,2
J,1,7,3
K,1,7,4
P,1,7,5
A,1,8,1
C,1,8,2
F,1,8,3
H,1,8,4
J,1,8,5
L,1,8,6
A,1,9,1
C,1,9,2
F,1,9,3
H,1,9,4
J,1,9,5
M,1,9,6
A,1,10,1
C,1,10,2
F,1,10,3
H,1,10,4
J,1,10,5
K,1,10,6
P,1,10,7
B,2,1,1
E,2,1,2
O,2,1,3
M,2,1,4
B,2,2,1
E,2,2,2
N,2,2,3
Q,3,1,1
M,3,1,2
I need to loop through this file unknown number of times until I end up with the First file in the chain from Last file(end of chain) . It is not one to one in chain , so one file can belong to multiple files.
In output example above for 'A' filename we have multiple branches and sequence number for each branches.
This logic will require loops, so I am not sure whether this can be implemented using tranformer loop functionality or Basic routine in Datastage . Please help
Thanks in advance,
Dev
Datastage looping
Moderators: chulett, rschirm, roy
I tried to understand your explanation but there seems to be information missing because I cannot follow your example data elements.
Regardless, looping in a transformer would probably not be an efficient way of parsing an entire series of files multiple times. I suspect that writing a BASIC routine will be your best fit.
Regardless, looping in a transformer would probably not be an efficient way of parsing an entire series of files multiple times. I suspect that writing a BASIC routine will be your best fit.
Hi Andy, Thanks for the response, I have added some more comments .
Source table format is derived from tree structure where Col1 and Col2 contains file names and we have to
find full trace of that filename in below format.
Col1 is 'From filename' and col2 is 'To Filename'
Input Nodes in tabular format:
Source Table format:
Col1,Col2
I,H
G,C
H,F
F,C
J,D
P,K
J,H
K,J
L,J
M,J
M,O
O,E
N,E
D,A
C,A
E,A
E,B
M,Q
Col1 and Col2 are derived from nodes tree where end of chain nodes are A,B,Q
We have to start from end of node or root node.
In above example root nodes are A,B,Q , as they exists in col2 and not in col1.
Expected output format:
I do not have much experience of Basic Routines. Please help.
Thanks,
Dev
[note - Added some formatting to make it easier to read - Andy]
Code: Select all
P
|
I K L
\ \ /
H<-----J<----M
| | / \
F G | N O \
\ / | \/ \
C D E |
\ / / \ |
A B Q / This is root level /
Source table format is derived from tree structure where Col1 and Col2 contains file names and we have to
find full trace of that filename in below format.
Col1 is 'From filename' and col2 is 'To Filename'
Input Nodes in tabular format:
Source Table format:
Col1,Col2
I,H
G,C
H,F
F,C
J,D
P,K
J,H
K,J
L,J
M,J
M,O
O,E
N,E
D,A
C,A
E,A
E,B
M,Q
Col1 and Col2 are derived from nodes tree where end of chain nodes are A,B,Q
We have to start from end of node or root node.
In above example root nodes are A,B,Q , as they exists in col2 and not in col1.
Expected output format:
Code: Select all
Col, Root, Branch, Seq
A,1,1,1 /* Root Node with first branch of 'A'*/
E,1,1,2
N,1,1,3 /* End of this branch as there is no col2 for 'N'*/
A,1,2,1 /* Root Node with Second branch of 'A'*/
C,1,2,2
F,1,2,3
H,1,2,4
I,1,2,5 /* End of this branch as there is no col2 for 'I'*/
A,1,3,1 /* Root Node with third branch of 'A'*/
D,1,3,2
J,1,3,3
M,1,3,4 /* End of this branch as there is no col2 for 'M'*/
A,1,4,1 /* Root Node with fourth branch of 'A'*/
C,1,4,2
G,1,4,3 /* End of this branch as there is no col2 for 'G'*/
A,1,5,1 /* Root Node with Fifth branch of 'A'*/
E,1,5,2
O,1,5,3
M,1,5,4 /* End of this branch as there is no col2 for 'M', two branches are finishing at 'M' */
A,1,6,1 /* Root Node with Sixth branch of 'A'*/
D,1,6,2
J,1,6,3
L,1,6,4 /* End of this branch as there is no col2 for 'L'*/
A,1,7,1 /* Root Node with Seventh branch of 'A'*/
D,1,7,2
J,1,7,3
K,1,7,4
P,1,7,5 /* End of this branch as there is no col2 for 'P'*/
A,1,8,1 /* Root Node with Eighth branch of 'A'*/
C,1,8,2
F,1,8,3
H,1,8,4
J,1,8,5
L,1,8,6 /* End of this branch as there is no col2 for 'L',two branches are finishing at 'L' */
A,1,9,1 /* Root Node with Nineth branch of 'A'*/
C,1,9,2
F,1,9,3
H,1,9,4
J,1,9,5
M,1,9,6 /* End of this branch as there is no col2 for 'M', third branch finishing at 'M' */
A,1,10,1 /* Root Node with Tenth branch of 'A'*/
C,1,10,2
F,1,10,3
H,1,10,4
J,1,10,5
K,1,10,6
P,1,10,7 /* End of this branch as there is no col2 for 'P',two branches are finishing at 'P'*/
B,2,1,1 /* Root Node with First branch of 'B'*/
E,2,1,2
O,2,1,3
M,2,1,4 /* End of this branch as there is no col2 for 'M'*/
B,2,2,1 /* Root Node with Second branch of 'B'*/
E,2,2,2
N,2,2,3 /* End of this branch as there is no col2 for 'N'*/
Q,3,1,1 /* Root Node with First branch of 'Q'*/
M,3,1,2 /* End of this branch as there is no col2 for 'M'*/
I do not have much experience of Basic Routines. Please help.
Thanks,
Dev
[note - Added some formatting to make it easier to read - Andy]