Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am very new to qlikview. I have a very straightforward excel data file that I would like to analyse, example below:
Country | Region | Age (0-20 years) | Age (21-40) | Age (41 +) |
---|---|---|---|---|
England | Yorkshire | 10% | 20% | 70% |
England | County Durham | 5% | 30% | 65% |
Scotland | Fife | 10% | 40% | 50% |
Northern Ireland | Belfast | 20% | 20% | 60% |
I would like to create a bar chart with the age variables (last 3 columns on the right) as the X axis and the percentage data as the values in the chart (y axis 0% - 100%). I loaded the data into qlikview in a standard way and created 3 seperate expressions for the Age Bands (which can be seen in the legend). I then used list boxes for country and region so that these can be selected and the bar chart changes - lovely. A couple of questions:
Thanks
In your script, do the following:
TableName:
CrossTable(Age, Proportion, 2)
LOAD Country
Region
Age (0-20 years)
Age (21-40)
Age (41+)Age (0-A20 yeaAs)
FROM YourFile.csv;
Hope this helps!
class( expression, interval [ , label [ , offset ]] )
Creates a classification of expressions. The bin width is determined
by the number set as interval. The result is shown as a<=x<b, where
a and b are the upper and lower limits of the bin. The x can be
replaced by an arbitrary string stated in label. 0 is normally the
default starting point of the classification. This can be changed by
adding an offset.
Examples:
class( var,10 ) with var = 23 returns '20<=x<30'
class( var,5,'value' ) with var = 23 returns '20<= value <25'
class( var,10,'x',5 ) with var = 23 returns '15<=x<25'
Sorry, I suspect I have made the mistake of trying to make this very complicated and the actual issue is the way I am loading the table. How do I make the above excel flat table load into a long thin Qlikview table that would look something like this (begining of table shown as example)?
Country | Region | Age | Proportion |
---|---|---|---|
England | Yorkshire | 0-20 | 10% |
England | Yorkshire | 21-40 | 20% |
England | Yorkshire | 41+ | 70% |
England | County Durham | 0-20 | 5% |
England | County Durham | 21-40 | 30% |
England | County Durham | 41+ | 65% |
So the 3 age columns become a new column and the data is all placed into the final new end column?
Thanks,
In your script, do the following:
TableName:
CrossTable(Age, Proportion, 2)
LOAD Country
Region
Age (0-20 years)
Age (21-40)
Age (41+)Age (0-A20 yeaAs)
FROM YourFile.csv;
Please do it as suggested by Nicole! That will work out...
Thanks folks, that is really haelpful and works!
I just need to work out exactly what has happened in the following line.
CrossTable(Age, Proportion, 2)
Age and Proportion are the new variables. What exactly is the number 2 doing, To my mind it takes every variable after the first two and pivoting the data into the new format is this correct and is there a better explanation?
What you're thinking is correct. The 2 means keep the first 2 columns the way that they are, and then pivot the rest of the columns into two new columns named Age and Proportion.
Thanks, that is a much clearer way of putting it. I now have a crosstable \ fill question but will put this in a new discussion.