topic Re: Join and Nulls in QlikView

Join and Nulls

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

I have two tables that automatically join on ID (Table Viewer shows two tables), where not all ID's in Table 1 are in Table 2. I thought when when I put this information in a table that the '-' was representing a null but if do IsNull(Indicator) I only get a result where the indicator exists. If the indicator represents condition X, how can I show the opposite as condition Y?

For example, I tried If( Indicator = 1, 'X', 'Y') but nothing is returned for fields where the Indicator is '-'.

TABLE 1
ID	Client
1	A
2	B
3	C
4	D

TABLE 2
ID	Indicator
1	1
2	1

Result
ID	Client	Indicator
1	A	1
2	B	1
3	C	-
4	D	-

Re: Join and Nulls

vishsaggi — Tue, 31 Jul 2018 14:31:19 GMT

May be try like

= IF(ISNULL(Indicator) = -1, 'Y', 'X')

Re: Join and Nulls

Anil_Babu_Samineni — Tue, 31 Jul 2018 14:31:34 GMT

What is the output you are expecting in a table?

Re: Join and Nulls

dapostolopoylos — Tue, 31 Jul 2018 14:34:59 GMT

You can set a default value for null or missing in the Result object

Re: Join and Nulls

greend21 — Tue, 31 Jul 2018 14:37:02 GMT

It doesn't return a result at all when there is nothing in the Indicator field

Re: Join and Nulls

greend21 — Tue, 31 Jul 2018 14:38:22 GMT

This is what I'm trying to achieve:

Goal Result
ID	Client	Indicator	Status
1	A	1	X
2	B	1	X
3	C	-	Y
4	D	-	Y

This is what happens:

Goal Result
ID	Client	Indicator	Status
1	A	1	X
2	B	1	X
3	C	-	-
4	D	-	-

Re: Join and Nulls

vishsaggi — Tue, 31 Jul 2018 14:38:45 GMT

Is this not what you are expecting?

Re: Join and Nulls

greend21 — Tue, 31 Jul 2018 14:39:59 GMT

Unfortunately I'm actually using Qlik Sense but I know the Qlikview forums get more responses. I forgot about that feature in QV but it's not available in QS. I was thinking it would be more of a formula issue so I thought posting here would be helpful.

Re: Join and Nulls

greend21 — Tue, 31 Jul 2018 14:44:39 GMT

Yes, but this is what happens.

Re: Join and Nulls

greend21 — Tue, 31 Jul 2018 14:47:00 GMT

I tried this as an expression and it works, but I need it as a dimension so there is a selectable value there. Any idea how to get it to work as a dimension?

Re: Join and Nulls

vishsaggi — Tue, 31 Jul 2018 15:03:59 GMT

Try this:

= Aggr(IF(ISNULL(NoteInd)=-1, 'Y', 'X'), ID, ID [Status])

Re: Join and Nulls

greend21 — Tue, 31 Jul 2018 15:08:52 GMT

Works perfectly! Thank you!

Re: Join and Nulls

Anil_Babu_Samineni — Tue, 31 Jul 2018 15:09:16 GMT

If you are expecting in Front end, Try this way?

If(Match(Indicator,1),'X','Y')