topic Re: How to correct cell formatting when loading from Excel? in QlikView

How to correct cell formatting when loading from Excel?

datanibbler — Wed, 12 Jun 2013 13:22:58 GMT

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

Re: How to correct cell formatting when loading from Excel?

datanibbler — Wed, 12 Jun 2013 13:39:18 GMT

P.S.:

I have tried nevertheless (inserting a new sheet), but the issue remains the same: Though I format the cells correctly beforehand, QlikView reads all three lines as a date - when I click on "embedded labels", the first line (what should be a date) becomes a number 😉 So it probably isn't because of the data being so "far down" on the original sheet. Also, there were two or three merged cells (vertically) in the original sheet, I deleted those to rule out that possible source of error - to no avail...

Re: How to correct cell formatting when loading from Excel?

prodanov — Wed, 12 Jun 2013 13:45:16 GMT

try to reformat manual field with num(<FieldName>) as <qvFieldName>.

Re: How to correct cell formatting when loading from Excel?

Wed, 12 Jun 2013 13:45:37 GMT

Hi,

Hallo,

Can you post an Exemple?

Kannst du post eine Beispiel?

Rebeca

Re: How to correct cell formatting when loading from Excel?

datanibbler — Wed, 12 Jun 2013 13:58:12 GMT

Hi,

sample files are always tricky - all the data is sensitive, so I'd have to construct one, and it would probably work then - that's the usual "show effect"

@ prodanov

I have thought of reformatting, but I don't know where to put that in with a crosstable?

Strangely, all those values are in the LOAD statement like [41257] (in brackets), like there were some blanks in that value - does that give you a hint as to the probable reason of the problem?

Re: How to correct cell formatting when loading from Excel?

Or — Wed, 12 Jun 2013 14:08:01 GMT

Friedrich,

I'm a little confused - if your data doesn't have embedded labels, why are you telling QV to look for them? [41257] is just the numerican interpretation of a date value (Dec. 14th, 2012, to be exact).

In any case, as suggested above, simply use e.g. Num([41257]) as [41257] in your load script. Alternatively, if you don't want to muck about with the crosstable values - often not a good idea - you can just run a second Load statement on your crosstable results (using Load * Resident X) and reformat there.

Hope this helps..

Re: How to correct cell formatting when loading from Excel?

prodanov — Wed, 12 Jun 2013 14:18:11 GMT

When load Excel file you can use @1 for Column1, @2 for Column2 and etc. And your load script will be:

crossable(name, value,2)

load

text(@1) as fld1

text(@2) as fld2

num(@3) as num1

num(@4) as num2

from <excel file> (biff, no labels, table is [Sheet1$])

Re: How to correct cell formatting when loading from Excel?

datanibbler — Wed, 12 Jun 2013 16:17:33 GMT

Hi,

I've created a small dummy table. Those date_values in the first row are actually just references to row5, I've just value_pasted them for demonstration - but I actually have to leave the references as such so that the sheet will be populated day-by-day.

I need only those three rows - in the diagram, only the days within the current month (or maybe the second half of the last) will be shown, but I need them all because I want to display avg values per month.

Reformatting line by line in the LOAD statement kind_of works, but it's a lot of work - and I don't know, the date_values should remain recognizable, so I cannot give them statical alias_names like >field1<, >field2< etc.

@ orsh_

You mentioned a RESIDENT load - that sounds like a good idea, but how would I do that when I have a CROSSTABLE load to begin with? I have no idea actually...

It's really frustrating when you want to load just three lines from an excel file - what could be easier (or so it seems) - and you just can't seem to make it...

Thanks a lot!

Best regards,

DataNibbler

Re: How to correct cell formatting when loading from Excel?

prodanov — Wed, 12 Jun 2013 22:24:19 GMT

View this file

2013/6/12 Friedrich Hofmann <qcwebmaster@qlik.com>

**
QlikCommunity <http://community.qlik.com/index.jspa>
Re: How to correct cell formatting when loading from Excel? created by Friedrich
Hofmann <http://community.qlik.com/people/datanibbler> in *Development
(QlikView Desktop)* - View the full discussion<http://community.qlik.com/message/356381#356381>

Re: How to correct cell formatting when loading from Excel?

datanibbler — Thu, 13 Jun 2013 08:11:29 GMT

Hi prodanov,

the TRANSPOSE command is one I didn't yet know. It seems that in your example, QlikView at least recognizes the cell formatting correctly.

I'll try that. I guess I'll have to write that manually, so I have to combine your LOAD statement with parts of the "original" one that I can generate so that it fits my actual file - the line_filters are a bit more complex, to the effect that I load only those three lines.

I'll be back

Best regards,

DataNibbler

P.S.: It does not work yet - I get the error "field <Datumswert> not found. Maybe I have put in that TRANSPOSE statement in the wrong place? I've backchecked with your example and put it in just before the closing_bracket for the entire filters part.

But for the 210 or so lines that come before, my original file looks exactly like that dummy. I don't know what can be wrong?

P.P.S.: Well, my filters part looks different from yours as I have compound_filters with conditions. currently, my QlikView does not seem to recognize that TRANSPOSE statement at all. Well, yes, it must be recognizing it, otherwise your example would not work on my machine, so I must have been doing something wrong.

P.P.P.S.: OK, now the TRANSPOSE works - but I still get the error "field not found: <Datumswert>" - I really can make neither head nor tail of that, my line-row-structure looks exactly like yours, that line has that word in that cell and the writing is identical.

Re: How to correct cell formatting when loading from Excel?

prodanov — Thu, 13 Jun 2013 12:14:40 GMT

Hi,

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

Regards

Re: How to correct cell formatting when loading from Excel?

datanibbler — Thu, 13 Jun 2013 14:26:09 GMT

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.

Re: How to correct cell formatting when loading from Excel?

prodanov — Thu, 13 Jun 2013 15:07:14 GMT

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

Re: How to correct cell formatting when loading from Excel?

datanibbler — Thu, 13 Jun 2013 15:22:04 GMT

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

Re: How to correct cell formatting when loading from Excel?

datanibbler — Thu, 13 Jun 2013 15:30:44 GMT

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