topic Re: Edit Script to hide null values in QlikView

Edit Script to hide null values

Thu, 09 Jan 2014 16:47:04 GMT

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.

Re: Edit Script to hide null values

swuehl — Thu, 09 Jan 2014 16:48:45 GMT

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;

Re: Edit Script to hide null values

Thu, 09 Jan 2014 17:08:09 GMT

Thanks Swuehl.

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

Re: Edit Script to hide null values

rbecher — Thu, 09 Jan 2014 17:19:04 GMT

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

Re: Edit Script to hide null values

whiteline — Thu, 09 Jan 2014 17:55:40 GMT

Hi.

Interesting...

Also Concat() function can be used.

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

Re: Edit Script to hide null values

rbecher — Thu, 09 Jan 2014 18:08:53 GMT

Which errors?

Re: Edit Script to hide null values

Thu, 09 Jan 2014 18:21:22 GMT

Hi,

If its a table with very few columns you can try this:

Set NullInterpret = '-';

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;

Re: Edit Script to hide null values

whiteline — Thu, 09 Jan 2014 18:26:13 GMT

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.

Re: Edit Script to hide null values

rbecher — Thu, 09 Jan 2014 18:28:42 GMT

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

Re: Edit Script to hide null values

whiteline — Thu, 09 Jan 2014 18:44:14 GMT

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

Re: Edit Script to hide null values

Fri, 10 Jan 2014 00:41:54 GMT

Actually it works, I forgot the group by clause.

Thanks a lot to all of you.