Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm working on cleaning up data and laying it out in the best possible format for qlikview and hit sort of a roadblock. Looking for clarification on how to best handle this kind of information. Here's an example
Date | Fulltime | Part-time | Contractor |
---|---|---|---|
01/01/2014 | 10 | 5 | 2 |
01/08/2014 | 5 | 2 | 0 |
01/15/2014 | 10 | 1 | 2 |
Now the twist to this data set that makes this a bit more complicated is this data is for only one project. I need this information for 3 other projects and what i'm doing is trying to create a single line chart with other list boxes that can help narrow in on a single project. Any feedback would be greatly appreciated. Thanks in advance.
Cheers,
Ahmad
So i found the solution to this, for anyone interested i'll past below - it's really simple.
Date | Project | Fulltime | Part-time | Contractor |
---|---|---|---|---|
01/01/2014 | 1 | 10 | 5 | 2 |
01/08/2014 | 1 | 5 | 2 | 0 |
01/15/2014 | 1 | 10 | 1 | 2 |
01/01/2014 | 2 | 4 | 4 | 5 |
01/08/2014 | 2 | 2 | 4 | 5 |
01/15/2014 | 2 | 4 | 2 | 1 |
...
you need to lay out the information for all projects in a single view, and then you can import it as a cross table where the first two columns are qualifier fields and the next 3 include the data type.
CrossTable(Type, Data)
Load
Date,
Fulltime,
Part-time,
Contractor
From TableName;
Thank you for your response. can you clarify, so you're saying load it as a crosstable which i get. That would mean i need to create a table for all other projects, correct? Secondly, how will I be able to establish this information belongs to "Project1" for instance.
How many tables you have?
4 other tables including this one. I have 5 projects in total.
Can you provide sample files along with your requirements?
Attached, it's the exact same table I pasted up, just replicated 5 times (in different tabs) with their respective resource values.
any ideas on this would be greatly appreciated.
Use below in script
============================================
FOR EACH i in 'A', 'B', 'C', 'D', 'E'
Test:
LOAD Date,
Fulltime,
[Part-time],
Contractor
FROM
[Project Resource.xlsx]
(ooxml, embedded labels, table is [Project $(i)]);
NEXT
CrossTable(Type, Data)
Load * Resident Test;
Drop Table Test;
=============================================
Now create a Line Chart
Dimension
Date
Type
Expression
SUM(Data)
can you explain what this part of the script is:
NEXT
CrossTable(Type, Data)
Load * Resident Test;
Drop Table Test;
I tried loading it but i get an error for almost every line. thank you