Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Send me qvw and original excel to view where is a problem.
Regards
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.
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>) ...
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
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