Date column recognized as "alphanumeric" while profiling

Karthik_Anbusekar

i have some date columns in my table (like below example). Just done profiling on those columns it recognizes their datatype as alphanumeric instead of date.

after profiling i just right click and seen the values, there it shows as Date datatype

all rows have date only in MM/DD/YY format

because of this i am getting wrong profiling result,
how to correct this? Can Someone help me with this?

Henry Simms

Hi @Karthik_Anbusekar . It looks to me like the column datatype is Alphanumeric. It may be that when the Dataset was created, the Treat all columns as alphanumeric setting was used. This setting controls how values are parsed on load.

This won't prevent some (or all) the individual values in the column from being converted to another Datatype later, and Data Studio allows columns with mixed types.

In your example, selecting to view Values from the Profile view will automatically infer and display the "actual" (or more accurately the "potential" datatype for each unique value in selected column.

Because in your case all values are identical and can be inferred as a DATE, you see this datatype reported in the "Values" view (your second screenshot). You can check the stored Datatype using the Datatype function, which I've applied in a new "Stored Datatype" column.

ALPHANUMERIC remains the stored datatype, but you can change this in a Transform using functions like Auto Convert or To Date.

Karthik_Anbusekar

Thanks @Henry Simms

hope you stating the below option

But i am not getting this option in define setting page while loading data (creating dataset) from Database. Above image is taken while loading data from csv file.

Also, while load data from csv file i got option to change the recognized datatype in annotate columns (see below image)

But same option in Annotate column i did not get while load data from database.

So the issue is while loading dataset from database,
—>i didn't select any option (Treat all columns as alpha numeric, automatically detect datatype, allow mixed datatype), since i dont get such option.
—>Also while loading data from database, START_DATE & END_DATE column detected as Alphanumeric. but TIMESTAMP detected as DATE correctly. Getting wrong profiling result.
—>Loading same data from csv file, all 3 columns detected as DATE datatype correctly. also getting correct profiling result.

So, my requirement is while loading data from database, it should detect the datatype correctly in annotate column page itself and also get right datatype while do profiling. without doing Transformation (as you mentioned Auto convert or any other)

i did not understand why correct datatype is detected automatically

Kindly someone help me to solve this issue

Henry Simms

Yes, the parsing options are only related to loading of data sources that are not already typed (CSV, Excel etc),

When loading from a database table (External System) via a JDBC connector, the Datatypes used by Aperture on load will be derived from those used in the source table.

Using MS SQL Server as an example, a column with DATETIME type will load into Aperture as DATE type by default. In some cases there are JDBC driver settings that determine type mapping behaviour, for example see the comment on the datetimeoffset type in the MS SQL Server driver docs.

In your case it sounds like the values you're loading are being brought into Aperture as ALPHANUMERIC. Options are to either resolve this on the source table in you database, through JDBC settings (if relevant), or to do a conversion to DATE type in a View or Workflow once the values arrive and have been loaded into Aperture

Henry Simms

See this Aperture documentation for the handling of MS SQL Server DATETIMEOFFSET datatype specifically,