Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Edit Script to hide null values

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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;

View solution in original post

10 Replies
Highlighted
MVP
MVP

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;

View solution in original post

Highlighted
Not applicable

Thanks Swuehl.

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
Highlighted
Master II
Master II

Hi.

Interesting...

Also Concat() function can be used.

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

Highlighted
MVP & Luminary
MVP & Luminary

Which errors?

Vizlib Head of R&D
Highlighted
Not applicable

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;

Highlighted
Master II
Master II

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

Vizlib Head of R&D
Highlighted
Master II
Master II

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