Regarding variable type mismatch

[Former Member]

Hi

I was loading data in SAP PA 2.2 automated mode, and for some reason when I click the analysis button in Data description screen the tool can't recognise the date fields as date, it will take the date column as number. And If I manually edit the type to date and run the algorithm, the tool throws error. And sometimes it does recognise the date fields. I am taking data from flat excel files.

Want to understand is there any specific format of date for which the tool recognise it correctly?

[Former Member · Accepted solution]

Hi,

If the date variable is not recognized when clicking "Analyze", forcing it is not helpful. It means that Automated Analytics couldn't interpret the information correctly.

To change that, you need to go 1 step back (to the data source selection panel) and click on the Advanced Settings button :

Then, click on File Settings and check the box in front of Specify Date Format.  Specify from the list in which order Day, Month and Year are specified in your dataset and then enter the separator that is used as seen below.

Note that if your date variable is a string in a database and contains months names (e.g. "Jan" or "January"), it won't be possible to proceed this way. You'll need a real date variable and have to convert the string to a date or datetime.

In Excel, months may appear with their name but it's just a display option you can change so that numbers are displayed for months.

Hope this helps,

Armelle

[Former Member · Accepted solution]

Hey Armelle

When I clicked that icon you highlighted, I didn't find those settings. Did i skip any step? Please find the attached snapshot below..

[achab · Accepted solution]

same for me (XLS files). But I can see it for CSV files.

[Former Member · Accepted solution]

Hey Armelle

The above screenshot which I have pasted came when I opened the on of my file in .xlsx format. The solution suggested by you works for .CSV I guess. I converted the file from .xlsx to .csv and its working fine now. Many Many thanks!!

[Former Member]

Though I will try the suggestions you have posted. Thanks

[achab]

Which module are you using? Time series, other?

Thanks

Antoine

[Former Member]

yes, time series

[PPaolo]

Hello,

I can think of a couple of causes: Excel stores the dates as numbers and then displays as dates, there might be something wrong in the way one file is read by PA. Or you might have a single value badly written in one of the files which makes PA misunderstand the content.

Can you try and export both Excel files as CSV and look into them to check if the date fields are saved differently?

[achab]

For time series, check http://help.sap.com/businessobject/product_guides/pa22/en/pa22_ts_user_en.pdf P9

I am not super clear why the same formats are not currently listed on both guides. This is being investigated by our documentation team. Anyway please stick to the formats in the time series guide and check that all Excel cells are formatted fine as highlighted by Pierpaolo.

Thanks

Antoine

[Former Member]

Thanks guys for all the suggestions. I have to go through the suggestions.

But both the files are in CSV format. And for some reason now the tool is reconising the header row also as data now.

Antoine I m sending those two CSV files on your email Id. Please have a look

[achab]

Thanks to Armelle, I was able to test the definition of date format on your files.

This is working fine. Screenshots 1 and 2 shows this for Data1.csv and screenshots 3 and 4 for Data2.csv.

Thanks & regards

Antoine

[Former Member]

Thanks Antoine for trying it out for me

[Former Member]

Hi Antoine

Thank you. I did go through the documentation. ActuallyI have  two excel files with different data sets. But the date column is same in both of them are same. However for one file the tool is recognising it as date and for the other one it is not recognising!

[achab]

An option would be for you to post an extract of your Excel file with only this date column. So that I can give it a try.

[achab]

In a nutshell, what you have to do is to change the format in Excel to adapt to the new format.

[achab]

Hi,

Please refer to p.29 of http://help.sap.com/businessobject/product_guides/pa22/en/pa22_class-clust_user_en.pdf

Kind ask to you for next time :-).

Can you please make sure to start in our help portal (SAP Predictive Analytics 2.2 – SAP Help Portal Page) and search in our documentation to see if you can find insights before posting ?

Thanks & regards

Antoine

[achab]

Hi,

Can you please screenshot or indicate what your date format looks like in your data source?

Thanks

Antoine