topic reference specific cells in excel in QlikView

reference specific cells in excel

Fri, 29 Oct 2010 12:18:05 GMT

Hi All,

I am very new to qlikview but am loving what can be done with the product. I trying to create a proof of concept to present, I am try to turn the data that gets provided daily in an excels into a dashboard.

LOAD

date(date# (mid(filename(), 14, 6), 'DDMMYY'), 'DD MMMM YY') as OrderDate,

date(date# (mid(filename(), 16, 2), 'DDMMYY'), 'MMMM') as MonthDate,

Client,

[Incident Count],

[Primary Uptime],

[Primary Uptime %],

[Secondary Uptime],

[Secondary Uptime %],

[Total Uptime],

[Toatal Uptime %]

FROM

(biff, embedded labels, table is FrontPage$, filters(

Remove(Row, Pos(Top, 153)),

Remove(Row, Pos(Top, 152)),

and this goes on for a while deleting rows.

then pull the data from: -

A180:H187

Row 180 is used as the Embedded Labels.

But i have found that some of the spreadsheets are not identical, there are extra blank rows, which when pulled into are deleted out which moves the positioning of the data needed.

Is there a way of the blank rows not been deleted? Or to select the same reference point in the excel sheets?

Cheers,

reference specific cells in excel

prieper — Tue, 02 Nov 2010 15:45:08 GMT

If you are able to scan for certain keywords within the Excel, you may load the entire file and then determine of basis of a text-analysis, where the header is and which rows might have to be included.

HTH
Peter

reference specific cells in excel

boorgura — Tue, 02 Nov 2010 16:32:41 GMT

As you say, the difference in sheets is that there are additional blank rows - this might be a feasible option for you.

you can load rowno() for each line (which is not blank). and based on the row number you can embed you logic.

But even here the additional rows have to be complete blank rows only.

Let me know if it solves your issue.

reference specific cells in excel

Tue, 02 Nov 2010 16:37:13 GMT

Hi Peter,

Sorry how would i go about scanning the file? I know what the headings are and the are the same each time. i know where the they are in the original file A180:H187. but when coming in qlikview deletes the blank rows.

The key would be for qlikview not to delete the blank rows when it pulls in the excel file? do you know if there is a setting that i can change?

reference specific cells in excel

Tue, 02 Nov 2010 17:32:02 GMT

Hi Rocky,

Thanks the reply.

I have given that a go and it is showing that i dont just have black rows. The excel sheet goes to 187 rows, the rows that i need to use are 180 - 187.

When i have done rowno() is bring up a difference between of the sheet between 152 and 156.

Is it possible to just use the bottom 7 rows?

cheers,

reference specific cells in excel

boorgura — Tue, 02 Nov 2010 17:36:21 GMT

Yes it is possible to use the bottom n records.

Directly, we have the function called "first".

Which can used as

first n

LOAD * from abc.xls;

when you do the load you can do a tranform to invert it.

That should get you what you are looking at.

reference specific cells in excel

Tue, 02 Nov 2010 19:11:01 GMT

i thought you had it then, i did: -

First 9

LOAD @1,
@2,
@3,
@4,
@5,
@6,
@7,
@8
FROM

(biff, no labels, table is FrontPage$, filters(
Rotate(left),
Rotate(left),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 1)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1))
));

I was not able to load my enbeded labels because they are now at the bottom of the 9 rows. I can not rotate back around because the other rows are still there. i cant delete the rows then rotate because of the same problem as before there are different amounts or rows.

If you can help again i am very grateful.

reference specific cells in excel

boorgura — Tue, 02 Nov 2010 19:36:37 GMT

Can you post a sample excel?

and brief which records are to be loaded.

Thanks

reference specific cells in excel

Wed, 03 Nov 2010 09:11:41 GMT

excel one

reference specific cells in excel

Wed, 03 Nov 2010 09:17:28 GMT

excel two.

I thought it woyuld be easier for you to have a look at two and see the small difference between them. i have highlighted the infomation that i need in both of the excels. there are two sections yellow and blue both highlighted. Yellow is A180:H187, blue is B189:C191.

thanks for your help on this

reference specific cells in excel

Mon, 08 Nov 2010 15:58:37 GMT

Hi Rocky,

Just wondering if you had found any other possible option.

Cheers,

Joe

reference specific cells in excel

boorgura — Mon, 08 Nov 2010 18:06:06 GMT

Sorry Joe,

dint really find time to work on it.

Will keep you posted if I figure out something.

reference specific cells in excel

prieper — Tue, 09 Nov 2010 13:41:00 GMT

Within the concept to scan for certain keywords in an Excel, you may have a look into

http://community.qlik.com/forums/t/36505.aspx

Model is pretty simple and might be brushed up a bit for your purpose, but works.

HTH
Peter

reference specific cells in excel

Wed, 17 Nov 2010 13:51:50 GMT

Thanks for your help guys. I was able to access the raw data. Which meant i could do the calculations on the import and generate the information that i needed.

Raw data is much better!!!!!