topic Re: Missing Date Vaules in QlikView

Missing Date Vaules

ciaran_mcgowan — Wed, 19 Aug 2015 15:42:32 GMT

Hi all,

I have created a master calendar but there are some dates that are not loading into it. After investigating, I found the reason is because some of the dates are not complete and there is no way to change this from the data source.

Some dates are missing days and other are also missing months (below are the 3 types of date values)

03-JAN-2010

xx-NOV-2012

xx-xxx-2013

The problem is that I converted these text fields to date values before creating the master (below)

Date(Date#(Date,'DD-MMM-YYYY'),'DD/MM/YYYY')

but the master calendar just ignores them. That means that the values that are only missing days are not appearing in their corresponding Month when looking at an overview of the Year, they are displayed as Null dates.

Re: Missing Date Vaules

sunny_talwar — Wed, 19 Aug 2015 15:46:31 GMT

What exactly does your missing day and Day Month Field look like??

NOV-2012

2013

xx-NOV-2012

xx-xxx-2013

Something else???

Re: Missing Date Vaules

ciaran_mcgowan — Wed, 19 Aug 2015 15:49:00 GMT

xx-NOV-2012

xx-xxx-2013

The text & numbers are replaced with x

Re: Missing Date Vaules

sunny_talwar — Wed, 19 Aug 2015 15:54:09 GMT

May be this:

Date(Alt(Date#(Date,'DD-MMM-YYYY'),'DD/MM/YYYY'), MakeDate(Right(Date, 4), If(Mid(Date, 3, 3) = 'xxx', 1, Mid(Date, 3, 3)), If(Left(Date, 2) = 'xx', 1, Left(Date, 2)))) as Date

You might need to check for the parenthesis.

HTH

Best,

Sunny

Re: Missing Date Vaules

jagan — Wed, 19 Aug 2015 16:27:03 GMT

Hi,

Try like this

TableName:

LOAD

MakeDate(SubField(NewDate, '-', 3),

Match(If(Len(Trim(SubField(NewDate, '-', 2))) = 0, 'JAN', SubField(NewDate, '-', 2)), 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'),

Alt(Num(SubField(NewDate, '-', 1)), 1)) AS NewFormattedDate;

LOAD

Replace(DateFieldName, 'x', '') AS NewDate

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Missing Date Vaules

ciaran_mcgowan — Wed, 19 Aug 2015 16:34:11 GMT

Thanks for the help.

I was hoping to avoid setting unknown months and days to the beginning of the Month/Year but it looks like it can't be avoided. I already had a check to replace one incorrect date earlier in the script (below)

Replace(Date,'91','xx') AS Date

So I just adapted that:

Replace(Replace(Replace(Date,'91',1),'xxx','JAN'),'xx',1) AS Date

That's done the trick.