3 Replies Latest reply: May 9, 2013 9:16 AM by Benjamin Dykstra RSS

    Max Returning Null if no Max Value

      Hello Qliview community:

       

      I am trying to create a flag to mark items as distinct. I have a number of null values in TargetSSDateAdd, TargetSkuInv, and TargetSkuStoreInv. These fields may also contain a number of valid values. I need the flag to mark rows with the Max of these values, and then Null if there are no values. I cannot replace null with a fake value as these fields are used in other analysis. (e.g. replace null dates with 1/1/9999)

       

      pseudocode:

      If Max(TargetSSDateAdd) = Null, return Null, else return Max(TargetSSDateAdd)

       

      Any help would be greatly appreciated!

       

       

      POGtemp:

      Load Distinct

                 store_number, 

                  sku_number,

                  c_sku, 

                 Max(create_date)                     as create_date, 

                 Max(change_date)                    as change_date,

                 Max(TargetSSDateAdd)          as TargetSSDateAdd, 

                 Max(bin_target_level)          as bin_target_level, 

                 Max(TargetSkuInv)                    as TargetSkuInv, 

                 Max(TargetSkuStoreInv)          as TargetSkuStoreInv, 

                 c_sku                                                  as c_num,

                 '1'                                                    as DistinctPOG

      Resident CPMPOG

      Where IsNull(inactive_date) = -1

      Group By

                 store_number,

                  sku_number,

                  c_sku;

        • Re: Max Returning Null if no Max Value
          Peter Schulz

          Hi bdykstra,

           

          perhaps you could post some sample data.

           

          I would try something like this: If Max(TargetSSDateAdd) = '',0,Max(TargetSSDateAdd)) or try

           

          If len(Max(TargetSSDateAdd)) = 0,0,Max(TargetSSDateAdd)) ...

           

          I think you should check which valueTargetSSDateAdd has if it there is no max value. I'm not sure if it has to be zero or something like " - "...

           

           


          • Re: Max Returning Null if no Max Value
            Yusuf Ali

            Hi ,

                 Try the below code .

             

            if( IsNull(max(TargetSSDateAdd)),0,Max(TargetSSDateAdd))  as Flag

             

            //yusuf

            • Re: Max Returning Null if no Max Value

              If Max(TargetSSDateAdd) = '',0,Max(TargetSSDateAdd))

               

              The above worked for what I was looking to do; however, I ran into more problems with trying to use this logic. I was trying to join two tables where the fields being matched up were null--from what I found, this is not possible. Instead, I created a temporary field (which masked the null values) in my main table, and then pulled that value into my temporary table as well.

               

              Here is a sample of what I did:

               

              CPMPOG:

              Left Join (CPMPOG)

              Load Distinct

                         store_number,

                          e_sku,

                          c_sku,

                   If(Len(inactive_date) = 0, Timestamp('9/9/9999'), Timestamp(inactive_date))                    as temp_inactive_date

              Resident CPMPOG;

               

              POGtemp:

              Load Distinct

                         store_number,

                          e_sku,

                          c_sku,

                         Max(temp_inactive_date)                    as temp_inactive_date,

                         1                                             as DistinctPOG

              Resident CPMPOG

              Group By

                         store_number,

                          e_sku,

                          c_sku;

                         

              CPMPOG:

              Left Join (CPMPOG)

              Load Distinct

                         store_number,

                          e_sku,

                          c_sku,

                         DistinctPOG                     

              Resident POGtemp;

               

              Drop Table POGtemp;

              Drop Field temp_inactive_date;