Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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?)
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
Thanks Peter and Marcus,
It was very helpful ,I would incorporate your Suggestion from now.
Thanks again.
Kaushal