Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
we need to display different rows/records data in different columns
In source file we have only 2 columns.
Source file:
TopologyID ParentNode
121 101
121 100
122 102
122 103
Output required:
Topology ParentNode1 parentNode2
121 101 100
122 102 103
Can anyone help me on above requirement .
Thanks,
Rajesh
Hi,
Try Like this
Sourcefile:
Load * inline [
TopologyID, ParentNode
121,101
121, 100
122, 102
122 , 103
];
Test:
LOAD
TopologyID,
Min(ParentNode) As ParentNode1,
Max(ParentNode) AS ParentNode2
Resident Sourcefile
Group by TopologyID
PFA,
I am not sure what you are looking for, please brief the question more
may be this is what you looking for..
Table1:
Load
Topology,
ParentNode as Parent Node1
From …. Sourcefile1
Table2:
Load
Topology,
ParentNode as ParentNode2
From…. Sourcefile2
Hi Renjith,
Thanks for response ,
Our requirement is we have source file with 2 columns topology Id & it's Parent Nodes.
Each topology has only 2 nodes ( means 2 records for each topologyId ).
we need a table with below fields & one 1 record for each topologyId.
Topology ParentNode1 parentNode2
Now i am getting output using min & Max functions. Is there any alternate method for this requirement ?
Load Inscope_TOPOLOGYID,min(TOPNODEID) as Parent1,max(TOPNODEID) as Parent2
Resident LinearTop;
Thanks
Rajesh
Hi Rajesh,
Just use the crosstable keyword in top of the table it will convert the columns into rows
syntax: crosstable(col1, col2)
Hi,
Try Like this
Sourcefile:
Load * inline [
TopologyID, ParentNode
121,101
121, 100
122, 102
122 , 103
];
Test:
LOAD
TopologyID,
Min(ParentNode) As ParentNode1,
Max(ParentNode) AS ParentNode2
Resident Sourcefile
Group by TopologyID
PFA,
Your method should work. Just add
group by inscope_topologyid
you can also use firstsortedvalue() as an alternative to min/max
Rob
See the attached file