topic Re: Left join is correct but how to filter correctly in QlikView

How to filter to include null values too

Mon, 23 Jun 2014 03:20:15 GMT

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?

Re: Left join is correct but how to filter correctly

whiteline — Mon, 23 Jun 2014 05:39:29 GMT

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.

Re: Left join is correct but how to filter correctly

Anonymous — Mon, 23 Jun 2014 06:10:16 GMT

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

];

Join

Inline * LOAD [

ForecastID

];

Hope it helps you.

Thanks

Re: Left join is correct but how to filter correctly

Tue, 24 Jun 2014 01:24:44 GMT

Hi Satyadev,

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

Re: Left join is correct but how to filter correctly

Wed, 25 Jun 2014 03:36:15 GMT

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.

Re: Left join is correct but how to filter correctly

Wed, 25 Jun 2014 04:23:38 GMT

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.

Re: How to filter to include null values too

Anonymous — Wed, 25 Jun 2014 04:26:14 GMT

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

Re: Left join is correct but how to filter correctly

Wed, 25 Jun 2014 04:33:33 GMT

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.