How to filter to include null values too

[#1]

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?

[whiteline]

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.

[Anonymous]

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

[#4]

Hi Satyadev,

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

[#5]

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.

[#6 · Accepted solution]

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.

[Anonymous]

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

[#8]

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.