Edit Script to hide null values

[#1]

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.

[swuehl · Accepted solution]

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;

[#3]

Thanks Swuehl.

Actually The original table is resident from another table. I think thats why it's giving me invalid expression error.

[rbecher]

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

[whiteline]

Hi.

Interesting...

Also Concat() function can be used.

And comparing the result with Only() the errors can be cached.

[rbecher]

Which errors?

[#7]

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;

[whiteline]

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.

[rbecher]

But, if you have numerical a list of figures wouldn't help. Instead if Only() I would agree to use Min() or Max()...

[whiteline]

Agree, Min() and Max() seems even more useful.