7 Replies Latest reply: Jun 25, 2014 12:33 AM by Tricia Chan RSS

    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?

        • Re: Left join is correct but how to filter correctly
          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.

          • Re: Left join is correct but how to filter correctly
            Satyadev Jaiswal

            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

            • 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!

              • 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.

                  • 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.

                    • Re: How to filter to include null values too
                      Satyadev Jaiswal

                      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