Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to filter to include null values too

Hi, I have 2 simple tables which I have done a left keep and I correctly get this data in one table:

Activity No     ForecastID

1                     18

2                     18

3                     18

4                     18

5                      -

6                      -

1                     19

2                     19

3                      -

4                      -

5                      -

6                      -

When my script has only "where ForecastID = 18", it shows up correctly but when I don't use the where clause, all the above data is available.  And when I want to select Forecast=18 to filter, I'll just see the first 4.  But i would like the first 6.  What do I have to do?

1 Solution

Accepted Solutions
Not applicable

Re: Left join is correct but how to filter correctly

hi, I have gotten around the issue.  Attached is approximately what I'm after.  Hope this helps someone else in the same situation.

Notes:

- "resident load" was used as otherwise, the "alt" doesn't work on the original data table

- both the values "-" and "18" on the forecast listbox needs to be selected if the null values are to be visible.

If anyone knows how to always show the null values without the user needing to consciously select both the Forecast together with the null value, I'd be interested.

7 Replies
whiteline
Honored Contributor II

Re: Left join is correct but how to filter correctly

Hi.

It depends on why #5, #6 Activities have null forecast id.

You see the first 4 rows as it's exactly the way QlikView works and it's exactly what you ask it for.

Share some details or a sample application.

satyadev_j
Valued Contributor

Re: Left join is correct but how to filter correctly

Hi,

If you want to see all 6 activity no, you may use cartesian join to achieve this. By doing cartesian join, you will get always 6 activity no for each forecast ID. See below,

Inline * LOAD [

Activity No

1

2

3

4

5

6

];

Join

Inline * LOAD [

ForecastID

18

19

];

Hope it helps you.

Thanks

Not applicable

Re: Left join is correct but how to filter correctly

Hi Satyadev,

I think your answer is what I'm looking for.  I'll try that and post back soon.  Thanks!

Not applicable

Re: Left join is correct but how to filter correctly

hi,

Thanks Satyadev and whiteline for your inputs.

Satyadev, in my real tables, there is a huge amount of data, so that solution wouldn't work.

whiteline, I've attached a sample.  This sample table originated from 2 tables.

I am hoping to achieve this:

That when a value is selected from the listbox: eg. Forecast 18, show all ForecastID=18 and "-"

Likewise, if ForecastID= 6 is selected, show all ForecastID=6 and "-".

Thanks.

Not applicable

Re: Left join is correct but how to filter correctly

hi, I have gotten around the issue.  Attached is approximately what I'm after.  Hope this helps someone else in the same situation.

Notes:

- "resident load" was used as otherwise, the "alt" doesn't work on the original data table

- both the values "-" and "18" on the forecast listbox needs to be selected if the null values are to be visible.

If anyone knows how to always show the null values without the user needing to consciously select both the Forecast together with the null value, I'd be interested.

satyadev_j
Valued Contributor

Re: How to filter to include null values too

Hi,

How about concatenating those "-" with forecastID individually. So in first steps, join those two tables and in second steps, load those records which doesn't have forecastID and add forecastID manually. For example below,

1st Step:

     Create TempLoad table by joining table with forecast table.

2nd Step:

Main:

NoConcatenate

LOAD

     aaActivityNo,

     aaActualAmt,

     ffActivityNo,

     ffFcAmt,

     ffForecastID

Resident TempLoad

Where Len(ffForecastID)>0;

Concatenate (Main)

LOAD

  aaActivityNo,

     aaActualAmt,

     ffActivityNo,

     ffFcAmt,

     18 as ffForecastID

Resident TempLoad

Where Len(ffForecastID)=0;

Concatenate (Main)

LOAD

  aaActivityNo,

     aaActualAmt,

     ffActivityNo,

     ffFcAmt,

     16 as ffForecastID

Resident TempLoad

Where Len(ffForecastID)=0;

DROP Table TempLoad;

Hope it helps you.

Satya

Not applicable

Re: Left join is correct but how to filter correctly

thanks Satyadev, that would work if I have a small amount of data.

Since I've got many forecast values and it must work with future forecast IDs, then hardcoding it would not pick up the new ones.

Community Browser