topic Re: Excel Custom Number Format Import in QlikView

Excel Custom Number Format Import

Anonymous — Fri, 20 Jan 2017 11:22:41 GMT

Hi all

my question is: Is there a way I can import this custom number format to qlikview ?

Re: Excel Custom Number Format Import

petter — Fri, 20 Jan 2017 17:12:39 GMT

Yes you can

You can use the Num# function in your LOAD statement in the load script:

Num#( fieldnameA ,'#,##0 ST')

Re: Excel Custom Number Format Import

Anonymous — Sat, 21 Jan 2017 13:53:49 GMT

Thanks for your reply.

Unfortunately, re-formating the imported number is not what I am after.

What I need is a way to read the number format that is specified in the Excel custom number format, not (only) the actual value of the field.
The reason is that the field contains numeric values, but each row has a different excel number format, which for me is information.

For instance, in the same field, there are some rows that have this format:
#,##0, "ST"

while others have

#,##0, "L"

...and then a few more different formats.

Is there a way to do read the format in the script?

(Excel file can't be altered, there is no other column that gives away what format my column would contain)

Re: Excel Custom Number Format Import

petter — Sat, 21 Jan 2017 20:12:42 GMT

So what do you want to do with these different formats once you have read them? I guess just reading the formats alone will not be of much value. Using them together with the numbers that are formatted according to that would probably be a goal then?

Do you have a part of a spreadsheet that you could share with a more extensive explanation? That way we won't waste yours or ours time.

By creating an Excel VBA macro you can easily extract any numberformat out of a spreadsheet and determine exactly which values that are using them...

Re: Excel Custom Number Format Import

petter — Sun, 22 Jan 2017 00:17:34 GMT

One solution could be to:

1) Copy the Excel-file to a temporary file folder

2) Open the copied Excel-file

3) Run a tiny VBScript macro that copies the format values to an adjoining column

4) Save the Excel-file

5) Read it from QlikView load script.

Another solution could be:

1) Run a tiny VBScript macro to copy the format values (all of them) into a QlikView variable in the load script

2) Access the format for a particular value either in the load script or later in an expression in a chart.

SubField()-function can be used in both cases.

The Macro/Module VBScript for the second solution would look like this:

And using this VBScript function from a load scxript would be to simply use it like this and assign the return value to a QlikView variable:

You can pick the value you want like this:

SubField( allNumberFormats , Chr(13) & Chr(10) , 59 ) // To get the 59th NumberFormat

Just displayed in a Text Box in QlikView they would look like this: