topic Re: Reading date format from a file name in QlikView

Reading date format from a file name

Wed, 08 Apr 2015 12:02:34 GMT

Hi All,

I have a file with filename as abc_201501.xls.

I have to separate the date part from the filename and check whether it has the format of YYYYMM. I have managed to separate just the date part from the file name using the subfield function. I am now having 201501 stored in a field. How do I now check whether this format matches with the date format YYYYMM.

Thanks,

Asma

Re: Reading date format from a file name

Ralf-Narfeldt — Wed, 08 Apr 2015 12:13:58 GMT

IsNum(Date#(InputField, 'YYYYMM'))

where InputField is the field containing 201501 for example

It will return true if it's a valid date

Re: Reading date format from a file name

anbu1984 — Wed, 08 Apr 2015 12:14:00 GMT

Try this expr. If it is valid YYYYMM then it returns -1 else 0

IsNum(Date#('201501','YYYYMM'))

Re: Reading date format from a file name

peter_turner — Wed, 08 Apr 2015 12:16:45 GMT

Hi Asma,

You would need to get QV to try and recognise that as a date using the date#(MyDate,'YYYYMM') function, where the MyDate is your date from the file.

If QV is able to recognise that as a valid date it will also have a numeric equivalent such as 42005 which you can test against. the test would fail if the format was wrong or return a null.