Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

How to correct cell formatting when loading from Excel?

Hi,

I have to load data (in crosstable_format) from an Excel file. Unfortunately, the data begins in row 222 with the date and in row 223 and 224 is the data. I can set the header to 221 lines and set embedded_fieldnames, no problem there, but somehow QlikView loads the data as a numeric value and the two numeric values below as dates.

I have already checked the formatting in Excel and redone it just in case. The date is correctly formatted as date and the two values below have the formatting "General".

I have checked the transformation wizard in QlikView, but it does not seem to offer cell reformatting.

Can anybody help me there? Just inserting another sheet in the Excel file with formulas is a solution, but the last one I would like to choose.

Thanks a lot!

Best regards,

DataNibbler

14 Replies
prodanov
Partner - Creator
Partner - Creator

Hi,

     Send me qvw and original excel  to view where is a problem.

Regards

datanibbler
Champion
Champion
Author

Hi prodanov,

there is the problem: I cannot send you the original Excel_file, there is a lot of sensitive company-internal data in there. All I can do really is create a dummy Excel consisting of just those few lines that I need as I've already done.

As to the qvw, I don't think you'd need the original one - I have also tested that new part with a blank qvw file, to later incorporate it into the existing file, taking care from the start to keep the field_names different. The outcome, however, was up to now no different insofar as it didn't work.

Best regards,

DataNibbler

P.S.: I just tried once more to create a dummy that's more like the original by deleting all the tabs that are not relevant - but I cannot. The file is protected, so there might be a problem that doesn't remain when I copy that one sheet to a new file instead. I'll try to find out if someone has the key and will give it to me.

prodanov
Partner - Creator
Partner - Creator

Hi,

   Try to use wizzard.

On transformation step:

   1. delete not used columns on GARBAGE tab

    2. on tab Rotate select transpose

and the wizzard will write the script. Then you can manual retransform datatype with date(<field>), num(<field>) ...

datanibbler
Champion
Champion
Author

Hi prodanov,

I think now I have identified pretty much what the problem is: A LOAD statement along the lines of your example works fine as long as I have no lines above- when I insert only one line above, it doesn't work anymore.

what can I do there? I don't really want to insert a new sheet for every KPI I have to build a diagram for, the file is already too blown-up...

Best regards,

DataNibbler

datanibbler
Champion
Champion
Author

I can't believe it: This actually WORKS! I followed the proposal in your last post and used the wizard. The outcome is quite a complex LOAD statement since I first remove all but three lines, then transpose and remove another two - but it works!

Thank you so much!

Best regards,

DataNibbler