topic Re: Find out if something is NOT a valid date in QlikView

Find out if something is NOT a valid date

datanibbler — Mon, 30 Mar 2015 09:07:56 GMT

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

Re: Find out if something is NOT a valid date

hic — Mon, 30 Mar 2015 09:10:54 GMT

Use the IsNum().

If it is recognized as a date, then it has a numeric value. E.g.

IsNum(Date#( A, 'M/D/YY' ))

HIC

Re: Find out if something is NOT a valid date

MK_QSL — Mon, 30 Mar 2015 09:14:48 GMT

For any Valid date if you write

=Num(Date)

you should able to get some numbers....

If the numbers are not appearing, either your date format is wrong or it may not be a date

Re: Find out if something is NOT a valid date

datanibbler — Mon, 30 Mar 2015 09:15:20 GMT

Hi Henric,

that doesn't seem to work.

All the dates come through as five-digit numeric values.

The date_format in the script is set to 'DD.MM.YYYY'

<=> When i write the formula

>> = ISNUM(DATE#([field], 'DD.MM.YYYY') <<

=> I always get 0, regardless whether the record is a date or one of the codes I need.

Can you tell me what's wrong?

Thanks a lot!

Re: Find out if something is NOT a valid date

MK_QSL — Mon, 30 Mar 2015 09:21:04 GMT

Try

=NUM(Date#(YourDateField,'DD.MM.YYYY')) as YourDateFieldNumFormat

Re: Find out if something is NOT a valid date

anbu1984 — Mon, 30 Mar 2015 09:23:20 GMT

Can you post sample excel file

Re: Find out if something is NOT a valid date

datanibbler — Mon, 30 Mar 2015 09:26:25 GMT

Hi,

maybe I have been imprecise in my original post:

- ALL of the entries in column _A are seemingly numeric since the ISNUM() function in a filter didn't filter out anything
=> That returned both the codes and the dates.

- Using DATE# with the date_format that is set on the Main_tab of my script, I get the number I have everywhere - DATE#([field], 'DD.MM.YYYY') returns just what is in the field, nothing else.

The dates in the Excel_file are written in DD.MM.YYYY format, but they are displayed in QlikView as five-digit numerics. The codes vary in length - so I cannot say for sure that there might not be five-digit codes, too.

I can try to put together a fake_app, but then the error might or might not come across ...

Best regards,

DataNibbler

Re: Find out if something is NOT a valid date

hic — Mon, 30 Mar 2015 09:29:31 GMT

Can you give an example of how these dates look? And an example of what the non-dates look like?

HIC

Re: Find out if something is NOT a valid date

datanibbler — Mon, 30 Mar 2015 09:35:22 GMT

Sorry,

this problem just dissolved into thin air 😉

It turns out I was looking at the wrong column - I have to load a different one where the filtering will be easier.

Still, I attach a small fake_app I have put together. From the Excel it is easy to see what is a note - I don't need those. That's why I thought I had to filter. It would still be interesting to know how I could do that.

Thanks a lot!

Best regards,

DataNibbler

Re: Find out if something is NOT a valid date

avinashelite — Mon, 30 Mar 2015 09:50:01 GMT

Hi DataNibbler,

I think few of the data is not in correct format, better you create a new date using MAKEDATE() function by extracting the number using left(), right and mid functions.

Re: Find out if something is NOT a valid date

datanibbler — Mon, 30 Mar 2015 10:35:29 GMT

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.

Re: Find out if something is NOT a valid date

anbu1984 — Mon, 30 Mar 2015 11:15:14 GMT

LOAD Code,

Country

FROM

fake_mapping_countries.xls

(biff, embedded labels, table is Sheet1$)

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

Re: Find out if something is NOT a valid date

avinashelite — Mon, 30 Mar 2015 12:38:02 GMT

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

Re: Find out if something is NOT a valid date

datanibbler — Tue, 31 Mar 2015 09:36:08 GMT

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!