Max Returning Null if no Max Value

[#1]

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;

[peschu123 · Accepted solution]

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

[#3]

Hi ,

     Try the below code .

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

//yusuf

[#4]

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;