Excel Intergration Problem

[abhaysingh]

Dear All,

I am attaching Excel sheet  with targets to my data model. I am associating or concatenating it with master date but not able to do.

Say, I am having date in excel 01/Jan/2014 with Target but when I do cancatenation It does not get associated with 01/Jan/2014 of Master date.

I have changed or used all the date formats but still not get the result.

Pls suggest.

Regards,

Abhay

[MK_QSL]

Create a List Box of your Date and try to see if 01/Jan/2014 is repeated with two different format or not?

[abhaysingh]

yes repeated

[MK_QSL]

That means your Date format of Excel file and your master calendar is different. You need to make the format same to join or associate both tables..

Please provide your sample excel file to check...

[Peter_Cammaert]

Usually Excel dates are recognized as such by QV. It seems that your Excel dates may have some unusual characteristics or that they include a time value..

Try to import your Excel dates as follows:

:

floor(date#(ExcelDateColumnName, 'DD/MMM/YYYY')) AS ExcelDateColumn,

:

Change the format specifier if this one doesn't match the Excel column format.

Best,

Peter

[Anonymous]

Peter,

Its not working.

[Anonymous]

Manish,

I have attached excel sheet, Let me know if you get any solution.

[#8]

Excel grinds my gears at the best of times when it comes to formatting..

Make sure it's not dumping it into a number inside square brackets ( eg [44190] ) by using this in your load script

Date(purgechar(exceldatefield,'[]'), 'DD/MM/YY') as datefield,

Then load it into your end table using

Load date#(datefield, 'DD/MM/YY') as DatePeriod,

[Peter_Cammaert]

Please post a screenshot of the Listbox that Manish asked you to create.

The problem isn't situated in your Excel, it's probably situated somewhere in your script. Can we have a look at your script or do we continue the guesswork?

[Anonymous]

Graeme,

I am not getting any '[]' like this in date field, I have done some transformation using rotate, left, right operation.

Syntax is:

LOAD Other,

     International,

     Domestic,

     Segment AS Date

FROM

CTM_Targets.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Rotate(right)));

I know this is syntax mismatch as it is working on Year, Month created with Excel date field.