Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like:
ID 1Rat 2Rat
1 1 -
1 - 2
2 3 -
2 - 5
How can I make them like this using edit script?
ID 1Rat 2Rat
1 1 2
2 3 5
Any help with be appreciated.
Maybe like this (assuming you have loaded table YourTable as shown above):
NewTable:
NOCONCATENATE LOAD
ID,
only(1Rat) as 1Rat,
only(2Rat) as 2Rat
Resident YourTable Group by ID;
drop table YourTable;
Maybe like this (assuming you have loaded table YourTable as shown above):
NewTable:
NOCONCATENATE LOAD
ID,
only(1Rat) as 1Rat,
only(2Rat) as 2Rat
Resident YourTable Group by ID;
drop table YourTable;
Thanks Swuehl.
Actually The original table is resident from another table. I think thats why it's giving me invalid expression error.
You will need square brackets for the fields 1Rat, 2Rat:
YourTable:
LOAD ID, if(len(trim(F1))=0, Null(), F1) as [1Rat], if(len(trim(F2))=0, Null(), F2) as [2Rat];
LOAD * INLINE [
ID, F1, F2
1, 1
1, , 2
2, 3
2, , 5
];
NewTable:
NOCONCATENATE LOAD
ID,
only([1Rat]) as [1Rat],
only([2Rat]) as [2Rat]
Resident YourTable Group by ID;
Drop Table YourTable;
- Ralf
Hi.
Interesting...
Also Concat() function can be used.
And comparing the result with Only() the errors can be cached.
Which errors?
Hi,
If its a table with very few columns you can try this:
Set NullInterpret = '-';
A:
LOAD * INLINE [
ID, Col1, Col2
1, 1, -
1, -, 4
3, -, 5
3, 4, -
];
Table:
Load ID,Col1
Resident A
where not isnull(Col1);
Join
Load ID,Col2
Resident A
Where not isnull(Col2);
Drop table A;
I mean that the source data structure can have errors: duplicates, more than two rows for each id.
Using both functions you can catch it.
But, if you have numerical a list of figures wouldn't help. Instead if Only() I would agree to use Min() or Max()...
Agree, Min() and Max() seems even more useful.