topic Re: Recognizing multiple date formats withing a column in QlikView

Recognizing multiple date formats withing a column

Mon, 28 Dec 2015 09:16:16 GMT

Hi ,

I have a table which consist of 10 columns ,out of which one is a date column and others are text and numeric fields,

I have written a code using TextCount and Numeric count, so as to identify format of the column whether it contains numeric values, text values, or both . How can i modify the current script so that it can identify that column contains date format.

NumericCount($(vFieldName)) as NumericCount,

TextCount($(vFieldName)) as TextCount,

if(TextCount($(vFieldName)) >= 1 and NumericCount($(vFieldName)) >= 1, 'mixed',

if(TextCount($(vFieldName)) >= 1, 'string', 'numeric')) as Format,

I want to add something which can identify it contains date. like Function_date($(vFieldName))=output as yes or no for date

Re: Recognizing multiple date formats withing a column

marcus_sommer — Mon, 28 Dec 2015 10:23:55 GMT

You could use the alt-function in a way like this:

alt(date#(date,'YYYY/MM/DD'), date#(date,'MM/DD/YYYY'), date#(date,'DD.MM.YY'),

'no valid date-format')

and/or

if(isnum(date) and date > 40000 and date < 44000, date, 'no date')

With the (bold) numbers you could specify the date-area which you are expect.

- Marcus

Re: Recognizing multiple date formats withing a column

Mon, 28 Dec 2015 10:27:57 GMT

Hi Marcus,

Thanks for replying.

So according to the code you mentioned , i need to have an idea about the distinct date formats present in my column, lets assume, i do not know before hand that what date formats are present in my data, i might have an idea about the date range of the data , ( I am loading the raw file directly into the Qlik View application)

what should i do in that case?

Re: Recognizing multiple date formats withing a column

Peter_Cammaert — Mon, 28 Dec 2015 10:35:16 GMT

You should have an idea about the date formats that will be present in your data source. Otherwise you can never decide on dates like 2/5/2015 (5th of february or 2nd of may?)

Re: Recognizing multiple date formats withing a column

marcus_sommer — Mon, 28 Dec 2015 11:00:42 GMT

Peter is right - you need a certain degree of knowledge about the data with whom you handled. You could of course extend the above mentioned checkings to further 10/20/... loops but in the end you will get blurring data without knowing the data-structure and data-quality. And are there any issues with them you should try to improve the quality and output from the source-data.

- Marcus

Re: Recognizing multiple date formats withing a column

Mon, 28 Dec 2015 11:12:38 GMT

Thanks Peter and Marcus,

It was very helpful ,I would incorporate your Suggestion from now.

Thanks again.

Kaushal