topic Re: Replace Empty Dates with Max Date in QlikView

Replace Empty Dates with Max Date

qlikwiz123 — Fri, 15 Nov 2024 23:26:25 GMT

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

Re: Replace Empty Dates with Max Date

edwin — Wed, 16 Dec 2020 14:56:40 GMT

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?

Re: Replace Empty Dates with Max Date

qlikwiz123 — Wed, 16 Dec 2020 15:01:08 GMT

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

Re: Replace Empty Dates with Max Date

qlikwiz123 — Wed, 16 Dec 2020 17:24:26 GMT

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.

Re: Replace Empty Dates with Max Date

edwin — Wed, 16 Dec 2020 18:58:33 GMT

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

Re: Replace Empty Dates with Max Date

edwin — Wed, 16 Dec 2020 18:59:15 GMT

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