Replace Empty Dates with Max Date

[qlikwiz123]

I have ID and Dates attached to them. But some IDs don't have any Date. I need to add Max of Date for those missing dates.

Table1:

LOAD

ID,

Date

From ...

Left Join(Table1)

LOAD ID,

if(len(Date)=0, Max(Date), Date) as Final Date

Resident Table1.

This keeps giving me Invalid expression error

[edwin]

i suggest get the maxdate first as a separate table and save the value in a variable.  then use that in your left join as

if(isnull(Date), Date('$(vMaxDate)') )as Final Date  //where vMaxDate is the variable containing max date.

assuming a missing date means it is null.

also, what will FInalDate be if date is not null?

[qlikwiz123]

Thanks Edwin.

If Date is not null (If there is a Date), then it should be the same date. If it is null, then I need to replace those null values with the Max(Date) value and create new field - FinalDate

[qlikwiz123]

This is still giving me empty dates instead of replacing them with Max Date. I see that vMaxDate is being populated but it somehow comes empty in the expression.

[edwin]

make sure variable for max date has the right date format.  remember that when you do a max of a date it becomes a number so you need to format it correctly.  otherwise, attach you script

[edwin]

even just the portion where you compute for max date and the part with if statement