8 Replies Latest reply: Jul 1, 2017 3:13 PM by Andrew Walker

# How can I filter by Empty Values?

Hi, I need to know how could i filter by empty values.

I Have 2 tables with a variables called:

1:

DEN_ITEMS, DESCRIPTION_OF_ERROR

1, A

2, B

3, C

4, D

5, E

6, F

7, G

8, H

2:

DEN_ITEMS, TYPE OF ERROR

1, Z

2, Y

3, X

6, W

8, V

Mainly, I need to know, which DEN_ITEMS doesnt have a TYPE OF ERROR.

I need a flag that allow me to filter by all the DEN_ITEMS that doesnt have.

Ex:

DEN_ITEMS, DESCR..., TYPE OF ERROR

4, D , Empty

5, E , Empty

7, G, Empty

Thanks

stalwar1

• ###### Re: How can I filter by Empty Values?

May be like this

Dimension

DEN_ITEMS

DESCRIPTION_OF_ERROR

Expression

If(Len(Trim([TYPE OF ERROR])) = 0, 'Empty')

• ###### Re: How can I filter by Empty Values?

Hi Sunny,

I put in the paper "Empty" to reference a DEN_ITEMS that doesn't have any TYPE OF ERROR.

When I try to filter it, as you called "empty", It doesnt work.

• ###### Re: How can I filter by Empty Values?

Filter? What are you filtering here?

• ###### Re: How can I filter by Empty Values?

I need to obtain by clicking a list box that give me the table :

4, D , -

5, E , -

7, G, -

From the table 1 and 2.

Sorry if my english is no formal.

Thanks

• ###### Re: How can I filter by Empty Values?

Would you be able to join the two tables like this?

Table1:

DEN_ITEMS, DESCRIPTION_OF_ERROR

1, A

2, B

3, C

4, D

5, E

6, F

7, G

8, H

];

Join (Table1)

DEN_ITEMS, TYPE OF ERROR

1, Z

2, Y

3, X

6, W

8, V

];

FinalTable:

NoConcatenate

DESCRIPTION_OF_ERROR,

If(Len(Trim([TYPE OF ERROR])) = 0, 'Empty', [TYPE OF ERROR]) as [TYPE OF ERROR]

Resident Table1;

DROP Table Table1;

• ###### Re: How can I filter by Empty Values?

Try using left join.

• ###### Re: How can I filter by Empty Values?

Things will be simpler and faster with Applymap()

MAP_ERR

DEN_ITEMS, TYPE OF ERROR

1, Z

2, Y

3, X

6, W

8, V

];

Table1:

LOAD *,Applymap('MAP_ERR',DEN_ITEMS,'Empty') as [Type of ERROR] INLINE [

DEN_ITEMS, DESCRIPTION_OF_ERROR

1, A

2, B

3, C

4, D

5, E

6, F

7, G

8, H

];

• ###### Re: How can I filter by Empty Values?

Hi Jaime,

Try a listbox with this expression:

=Aggr(Only({\$<DEN_ITEMS = E({<[TYPE OF ERROR] = {*}>})>}DEN_ITEMS),DEN_ITEMS)

returns 4,5,7.

Cheers

Andrew

Edit: This is an improvement

=Aggr(Only({\$<DEN_ITEMS = E({1<[TYPE OF ERROR] = {*}>})>}DEN_ITEMS),DEN_ITEMS)

Rather than explain it's interesting to compare the different behavour when clicking on listboxes with the different expressions.