topic Re: Text field changing to date format in excel in QlikView

Text field changing to date format in excel

ipauldur — Mon, 20 Feb 2012 02:26:10 GMT

Hi All,

I have the field name called "UnitNo" which contains the value like "B1-02", "10-01","10-02", etc..

I want to copy the report (Full table) and paste into excel. No need to do "Send to excel".

When i copy and paste into excel, the "Unit_no" column values changing into "d-mmm" format if there is no alphabets.

For example, the value "10-02", when i paste into excel became "2-oct".But if the value "10-02A" when i paste into excel still same "10-02A".

Can you please let me know how to format or any setting can be done for geting the same data in excel when we do copy & paste.

I am using Qv10 Sr3 and MS Excel 2007 version.

I attached the sample file for your reference.

Thanks,

Durai.

Text field changing to date format in excel

Anonymous — Mon, 20 Feb 2012 02:48:18 GMT

Durai,

Try to format it as text in the table: =text(UnitNo)
(An alternative is to use macro which can format data in Excel any way you want. This macro requires system access.)

Regards,
Michael

Text field changing to date format in excel

ipauldur — Mon, 20 Feb 2012 03:03:18 GMT

Hi Michael,

Thanks for your reply.

The Text(UnitNo) is already tried but didn't worked.

For the macro, do you mean that, we need to format the data in excel after pasting?. Because, i don't know how to format in excel for getting the same Qlikview value. If i format as "General" or "Text" for the UnitNo column in excel, still not able to get the same which we want.

Can you please suggest.

Thanks,

Durai.

Re: Text field changing to date format in excel

Anonymous — Mon, 20 Feb 2012 05:34:27 GMT

Hope this attached file will be helpful for your requirment.

Regards

Ashok

Text field changing to date format in excel

ipauldur — Mon, 20 Feb 2012 05:53:50 GMT

Hi Ashok,

Thanks.

Still not getting the exact value. Value "10-02" changing to 2-Oct in excel.

Are you getting "10-02" in your system?

Regards,

Durai.

Text field changing to date format in excel

Anonymous — Mon, 20 Feb 2012 06:07:29 GMT

Yes i got the exact value in my system

Re: Text field changing to date format in excel

Anonymous — Mon, 20 Feb 2012 06:09:58 GMT

See the Image which i got copy and paste the value into excel

Text field changing to date format in excel

ipauldur — Mon, 20 Feb 2012 06:16:28 GMT

Hi,

Which version of Qv and Excel you are using?.

Are you using Qv 10.00.9061.7 SR3 version?

Thanks,

Durai.

Text field changing to date format in excel

Anonymous — Mon, 20 Feb 2012 06:21:44 GMT

Version Qv 9.0

Text field changing to date format in excel

ipauldur — Mon, 20 Feb 2012 06:35:50 GMT

Hi,

May be, because of this version difference. I am not getting in Qv10 version unless untill adding any spl character with that field value. Anyway thanks for your time.

Regards,

Durai.

Text field changing to date format in excel

Anonymous — Mon, 20 Feb 2012 06:39:06 GMT

Please check this

Add one single quote before the unit no and copy paste to excel

Check wheter you have the same problem

=chr(39)&text(Unit_No)

Use this expression to check

Regards

Ashok

Text field changing to date format in excel

ipauldur — Mon, 20 Feb 2012 06:51:59 GMT

Hi,

I tried this. But didn't worked. It's working only when we add any character before or after the value.

Thanks,

Durai.