topic Re: Max Returning Null if no Max Value in QlikView

Max Returning Null if no Max Value

Thu, 11 Apr 2013 13:03:52 GMT

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

peschu123 — Tue, 16 Apr 2013 09:09:27 GMT

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

Tue, 16 Apr 2013 10:21:51 GMT

Hi ,

Try the below code .

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

//yusuf

Re: Max Returning Null if no Max Value

Thu, 09 May 2013 13:16:13 GMT

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;