I want to load the FORMULAE from Excel cells not the values

[sarahallen1]

Is this possible?  I want to define fields etc in my LOAD statement as usual, but for the field values I want to read in the formula in each Excel cell, rather than the value.

Any ideas would be very welcome!

Thanks.

[ngulliver]

Hi, Sarah.

If I have misunderstood your question, my apologies.

What would you be wanting to do with these formula once they are in QlikView ? Are you hoping to continue to use them to derive values from the other source fields ? In which case, this is not possible.

If you are simply wanting to look at the formula as a text field, what you can do is a mass replace in Excel of '=' with '' which will reveal all formula as text before importing it as field values

Regards,

Neil

[sarahallen1]

Hi Neil,

Thanks very much for your reply, it's appreciated.

I don't want to reuse the formulae in QV to derive values - I should have clarified this, so thanks for raising it. 

What I'm trying to do is use QV to audit the formulae in a series of about 450 spreadsheets (each spreadsheet corresponds to a stock).  At the moment I have a very nice QV system that reads in the values from all of these spreadsheets.  But a little data quirk has highlighted that one particular metric is calculated differently for different stocks - i.e. the formula is different in a given cell for different spreadsheets.  I want to know which stocks use each calculation method.

So unfortunately I don't think a mass replace within Excel will work as I'd have to do it within each of the 450 spreadsheets (also some formulae are quite complicated in Excel with nested IFs etc so might have more than one = sign in the formula, not just the one at the start).

Thanks for your help though!

Any other ideas much appreciated.

Sarah

[sarahallen1]

Does anybody know of a potential solution here?

[#5]

Hi Sarah,

We do it in our models but you will have to have the formula enclosed in these "" to prevent the formula from actualy calculating.

You can then import the excel spreadsheet into your QlikView model but I guess enclosing all of the expressions in excel with "" will be to much of a cumbersome task for you.

Regards,
Marius

[sarahallen1]

Thanks Marius.  Yes as you say, unfortunately it's not an option for us to make changes to the Excel spreadsheets (too many of them).

Does anybody know of a way that QlikView itself can directly read an Excel formula rather than an Excel formula's value?

[#7]

Hi Sarah,

I have tried different ways of loading the data and it works perfectly if you have it enclosed with "" but can't get it to work the other way around.

Sorry.

Lets wait and see if theres not somebody out there that might know.

Regards,
Marius

[jerem1234]

What you can try is:

Go into your excel files and click on "Show Formulas". Then save the excel file.

Then do a Save as for the same excel file, and save it as a text file(The formulas should stay intact and not be evaluated).

Then just load that text file into qlikview instead. The formulas should stay. Unfortunately, this requires a little work as well.

Hope this helps!

[sarahallen1]

Thanks Jeremiah.

We've done this with some Excel/QV applications, and it works well - but the application I am asking about looks at over 500 different Excel spreadsheets, so I'm looking for a solution which doesn't require anything being changed at source.  (I guess I could do a macro in Excel to convert cells to "show formulas" or put ' before the formulae - but I'd rather not have to resort to it as I want it to be a live analysis of the 500 Excel models that are being worked on).

What I'm looking for is something in the QV script which treats the Excel file differently (rather than biff or ooxml maybe).  It may be that such a connector does not exist - but it would be nice if it did!

[sarahallen1]

Has anybody else got any ideas on how to solve this?

I can't use a solution that requires me to do anything in Excel as there are hundred of spreadsheets - I use a QlikView *.xls type of load.  I need to load in the formulae without changing the spreadsheets themselves (as this is dynamic so I want to monitor the formulae across all spreadsheets that get added to that folder too).

Any ideas on whether this is possible?

SpyrosD80‌