Find out if something is NOT a valid date

[datanibbler]

Hi,

I have to load an Excel_file where in column A, I usually have a numeric code. I need that as a mapping_table.

The issue is, following the actual block of data comes a block of notes where in column A there is a date - the date that the note was entered, I guess. Never mind, I don't need those.

=> The question is, how can I find out and filter out the lines where I have a date in column A?

      The length of the codes differs from 1 to 7 digits.

       Between those two blocks of data, there are some blank lines.

Can anyone give me a hint? I guess I have to use the DATE#_function - but then, any number can  be interpreted as a date.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi Avinash,

yes, I had that before and I know the Makedate() function can be useful - but in this case I don't need a date - I need to find out what is and what is not supposed to be a date in the underlying Excel_file.

[anbu1984]

LOAD Code,

     Country

FROM

fake_mapping_countries.xls

(biff, embedded labels, table is Sheet1$)

Where IsNum(Code) And Index(Code,'/');

[avinashelite · Accepted solution]

Hi DataNibbler

I found a solution for this, convert your excel to csv format then load the data from it, then use isnum() dates will be loaded as string and rest of thing will be loaded as num. this will solve the issue.

Excel will not handle this text and number format efficiently, when you convert it to csv, it define the actual format.

Hope this helps you 

[datanibbler]

Hi Avinash!

This works. Unfortunately, the QlikView_wizard does not let you load an Excel file as a csv, so I would have to actually save a copy of that.

As I always try to generate as little redundancy (and thus error_potential) as possible, I will ultimately go for another approach, though - I will simply load an additional field which I can use to filter.

Thanks a lot anyway!