topic Re: How can I filter by Empty Values? in QlikView

How can I filter by Empty Values?

jaarroyl — Wed, 28 Jun 2017 17:26:28 GMT

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

I Have 2 tables with a variables called:

DEN_ITEMS, DESCRIPTION_OF_ERROR

1, A

2, B

3, C

4, D

5, E

6, F

7, G

8, H

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?

sunny_talwar — Wed, 28 Jun 2017 17:28:24 GMT

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?

jaarroyl — Wed, 28 Jun 2017 17:31:12 GMT

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?

sunny_talwar — Wed, 28 Jun 2017 17:34:27 GMT

Filter? What are you filtering here?

Re: How can I filter by Empty Values?

jaarroyl — Wed, 28 Jun 2017 17:37:25 GMT

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?

sunny_talwar — Wed, 28 Jun 2017 17:41:09 GMT

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

Table1:

LOAD * INLINE [

DEN_ITEMS, DESCRIPTION_OF_ERROR

1, A

2, B

3, C

4, D

5, E

6, F

7, G

8, H

];

Join (Table1)

LOAD * INLINE [

DEN_ITEMS, TYPE OF ERROR

1, Z

2, Y

3, X

6, W

8, V

];

FinalTable:

NoConcatenate

LOAD DEN_ITEMS,

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?

passionate — Sat, 01 Jul 2017 09:13:08 GMT

Try using left join.

Re: How can I filter by Empty Values?

vinieme12 — Sat, 01 Jul 2017 09:36:32 GMT

Things will be simpler and faster with Applymap()

MAP_ERR

MAPPING LOAD * INLINE [

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?

effinty2112 — Sat, 01 Jul 2017 13:08:14 GMT

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.