reference specific cells in excel

[#1]

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,

[prieper]

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

[boorgura]

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.

[#4]

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?

[#5]

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,

[boorgura]

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.

[#7]

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.

[boorgura]

Can you post a sample excel?

and brief which records are to be loaded.

Thanks

[#9]

excel one

[#10]

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