Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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;

Tags (4)
1 Solution

Accepted Solutions
peschu123
Not applicable

Re: Max Returning Null if no Max Value

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 Replies
peschu123
Not applicable

Re: Max Returning Null if no Max Value

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


Not applicable

Re: Max Returning Null if no Max Value

Hi ,

     Try the below code .

 

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

//yusuf

Not applicable

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;