topic Re: Excel Intergration Problem in QlikView

Excel Intergration Problem

abhaysingh — Tue, 24 Mar 2015 07:45:55 GMT

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

Re: Excel Intergration Problem

MK_QSL — Tue, 24 Mar 2015 07:52:02 GMT

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

Re: Excel Intergration Problem

abhaysingh — Tue, 24 Mar 2015 07:54:11 GMT

yes repeated

Re: Excel Intergration Problem

MK_QSL — Tue, 24 Mar 2015 07:58:42 GMT

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...

Re: Excel Intergration Problem

Peter_Cammaert — Tue, 24 Mar 2015 08:09:13 GMT

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

Re: Excel Intergration Problem

Anonymous — Tue, 24 Mar 2015 08:39:01 GMT

Peter,

Its not working.

Re: Excel Intergration Problem

Anonymous — Tue, 24 Mar 2015 08:39:52 GMT

Manish,

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

Re: Excel Intergration Problem

Tue, 24 Mar 2015 09:24:37 GMT

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,

Re: Excel Intergration Problem

Peter_Cammaert — Tue, 24 Mar 2015 09:47:36 GMT

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?

Re: Excel Intergration Problem

Anonymous — Tue, 24 Mar 2015 10:17:06 GMT

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.

Re: Excel Intergration Problem

MK_QSL — Tue, 24 Mar 2015 11:11:10 GMT

Where is EXCEL?