Need Comments On Excel File

[israrkhan]

Hi Community,

I Need your assistance on a excel file.

Scenario:

i have one excel document, having 12 sheets in the document, and each sheet having around 5000 rows.

in each sheet i have columns, like Region, Subject, and some other data columns.

What i want:

i have to read(load) around 100 rows from each sheet, but different rows number in all sheet.(depends on subject column)

let say in sheet1 , i have to read, row number 20 to 25, then 42 to 56 , then 102 to 126 and so on....

in Sheet2 , let say have to read 201 to 220, then 307 to 327 and 505 to 555 and so on .........

then shee3 , sheet4 and so on..

i have to make condition on Subject Column.

but the problems is, if i write 100 condition in each where clause, so my code will go very long..

like where Subject = A, or Subject = AA, mean i have to read 100 rows out of 5000, in each sheet.

how to make where clause, or is there any short way..

kindly ask me, if it is not clear. will try to explain in better way..

Your Comments, Ideas will be highly appreciated....

Regards

Khan

[#2]

What determines which rows have to be read ?

Load all of the rows into a temp table and use the criteria to generate a flag on the rows required.

Then reload the temp table into a new table with a where clause on the flag.

Drop the temp table .

You now have your data in qlikview table.

[#3]

Effectively, we have to determine the WHERE clause. I assume you have see the match() ans so on functions ...

Once, I have solved with :

a LOAD values into a Condition Field  (you must know what you have to load specifically: either INLINE or a new sheet with the condition)

a second load WHERE exists(CondField NewField)

Fabrice

[#4]

It would help to understand why the data is stored in this way and what it is about a row that indicates why it should in. How do you get the data into the spread sheet?

Without knowing more I'd be inclined to think that I would add a calculated field (a flag) to each row in the spreadsheet which indicated if it was to be read by QlikView. Then use the flag in the WHERE clause so

LOAD Field1, Field2, FlagField

FROM

   SpreadSheetName (table is SheetName)

WHERE

  FlagField = 'TRUE'

[israrkhan]

Hey Guys,

Thanks for your comments.

but let me explain in more detail,

i have one excel document, that has 12 sheet, 12 sheets are cities name,

in each city the company has around 5000 projects,

but for one of their manager  want a qlikview document, in that he want to read around 70, 80 projects from each sheet. have to chose rows depends on Project title.. like where project title = A, or AB, AC, KDS, THE and so on.

so if i write 70 project name in each sheet where clause, dont you think its a very long way to read data.

i was just looking ,that is there a short way instead of mentioning 70 projects in where clause.

is it clear..?

[israrkhan]

Hey Mark,

your answer make scene

let me know, you mean i should add one extra column for the rows, which i have to read, the i should use that column in where clause.

LIKE:

fielda, field2, field3, new_flage_field

where new_flage_field = 1,

you mean this...

[#7]

Without seeing the data and seeing exactly how it is structured I think I would just read in all the data and not use a SQL query to filter it.

I don't think you need to have 12 sheets. Each row should identifes which city it belongs to and which manager is responsible for the project. Then I would just use a list box to filter the city and a list box to filter the manager and then you could use something simple like the search on another list box for project name.

You may find that if the data is correctly structured that there are other columns that you can use to filter on to help narrow the projects to display.

One of QV's greatest strengths is in quickly narrowing down the data that you want to look at. So it makes sense to use the features QV provides to achieve this rather than try and do it with a complicated SQL query.

This will also make it more useable, as any manager from any city can examine the projects they are interested in.