How to correct cell formatting when loading from Excel?

[datanibbler]

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

[datanibbler]

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

[prodanov]

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

[#4]

Hi,

Hallo,

Can you post an Exemple?

Kannst du post eine Beispiel?

Rebeca

[datanibbler]

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?

[Or]

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

[prodanov]

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$])

[datanibbler]

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

[prodanov]

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>

[datanibbler]

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.