Data from different excel files. Query?

[#1]

Hello Everyone,

I am facing 2 problems while loading data from excel and further how to use that data to draw charts.

1) I am loading 2 excel files that has more then 10,000 records each. But while loading 2 files its kinda of slow  and its taking time and after sometime it just stops automatically and if you click on End it says " NOT RESPONDING" and then you have to close the file. So I don't know how to go about it? Should I give more time to run the script, I almost waited fr 30 mins.

2) From the different files that I am unable to load. I want to draw a graph that will caluclate no of males and females in 2009, 2010 etc. I am loading different files for 2009 , 2010 etc. I need to draw a graph that can take data columns from all the files like from 2009, 2010 etc spreadsheet , calculate the total number of males and females in all the years and display the output on one bar graph. This is done so that you can compare different values in different years. Please tell me what logic should be implemented to display this.

Thank you . I would appreciate your replies.

Best ,

Yaman

[johnw]

1) 10,000 records is a trivial amount of data for QlikView.  Chances are you've accidentally done some sort of Cartesian join(s), but that's difficult to diagnose remotely.  Make sure your joins are on the intended key(s).  From question #2 it sounds like you should be concatenating instead of joining anyway.

2) I'm unclear how you want it to look.  Maybe a stacked bar for each year with different colors for male and female?  Two dimensions, Year and Sex.  Expression something like count(distinct PersonID).

[#3]

Hello John,

See I have 2 excel files containing the same field (column names) but different data for 2009 and 2010. Now when I load one file it is loaded without any problem, but when i load the other one it generates an error and stop working.

Well I have no idea, if at all I need to use keys because right now I just want to load them and further will use them for my charts generation.

2) Also , my 2nd question is related to division. I have like 5 years of data(same as above) and I want to generate how many males and females did the company hire each year. All the data is in different spreadsheets for different years. Now , if I load all the files for different years,

How can I generate a graph (just one graph) which can take data from different spreadsheets( i.e. Sex column) and then count it and display it for different years. so that you can see the trends and compare values for different years.?

I hope I am clear in my question. Thank you for replying.

Yaman

[johnw · Accepted solution]

I think all I can do is try to say the same thing I said before in a different way.

You need to concatenate your spreadsheets together into a single table.  This would generally be the default behavior if they all have the same field names, but I like to do it explicitly:

MyData:
LOAD my fields
FROM my 2010 spreadsheet;
CONCATENATE (MyData)
LOAD my fields
FROM my 2009 spreadsheet;
etc.

If there's a pattern to the spreadsheet names, or if they're the only files in a directory or something like that, you should be able to do it with a single load, though.  I've not done a single load from multiple spreadsheets, but you should be able to look up how to do it same as I could.

If your spreadsheets do NOT have a Year field, you'll have to add one during the load.

Once you have a single table with all five years and a year field, you would build a chart something like this:

Dimension 1 = Year
Dimension 2 = Sex
Expression  = count(distinct PersonHiredID)

If you're trending, I'd make that a line chart instead of a bar chart, but it's the same basic definition.

[#5]

Hello John,

Yes i am able to load both the excel files through single load function. Now I have to work upon developing the charts using this.

Thank you so much .

Best ,

Yaman

[#6]

Can you please elaborate on loading identically named tables from mulitple spreadsheets during a single load?

I'd like to do something like

LOAD aa,bb,cc

from My\Spreadsheet\Location\*

Will this concatenate all the data into a single table, or create sythetic keys?

[#7]

To avoid having to concatenate all the data from spreadsheets into one table here is an alternative way of building a time series of cell values across multiple separate spreadsheet versions:

http://market.qlik.com/clusterseven-reporter.html

Regards