Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following sample data (can't find how to attach my sample QVW file?)
Data:
LOAD * INLINE
[
contact_id,purchase type,count2005,value2005,count2006,value2006,count2007,value2007,count2008,value2008,count2009,value2009,count2010,value2010,loyalty2005,loyalty2006,loyalty2007,loyalty2008,loyalty2009,loyalty2010,purchases2005,purchases2006,purchases2007,purchases2008,purchases2009,purchases2010
1,Lights,1,30.769,0,0,0,0,7,175,1,25,1,62.5,2yrs consecutive,Lapsed Buyer,Lapsed Buyer,Reactivated Buyer,2yrs consecutive,3yrs consecutive,Lights | Saddle,Saddle | Brake Pads,Saddle | Brake Pads,Lights | Saddle,Lights | Saddle,Lights | Saddle)
1,Bike Frame,0,0,0,0,0,0,0,0,0,0,0,0,Lapsed Buyer,Lapsed Buyer,Lapsed Buyer,Lapsed Buyer,Lapsed Buyer,Lapsed Buyer,Lights | Saddle,Saddle | Brake Pads,Saddle | Brake Pads,Lights | Saddle,Lights | Saddle,Lights | Saddle)
1,Saddle,10,250,12,300,12,300,5,125,9,225,12,300,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,Lights | Saddle,Saddle | Brake Pads,Saddle | Brake Pads,Lights | Saddle,Lights | Saddle,Lights | Saddle)
1,Brake Pads,0,0,1,7,1,25,0,0,0,0,0,0,Non Buyer,New Buyer,2yrs consecutive,Lapsed Buyer,Lapsed Buyer,Lapsed Buyer,Lights | Saddle,Saddle | Brake Pads,Saddle | Brake Pads,Lights | Saddle,Lights | Saddle,Lights | Saddle)
1,All Purchases,11,280.769,13,307,13,325,12,300,10,250,13,362.5,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,5+yrs consecutive,Lights | Saddle,Saddle | Brake Pads,Saddle | Brake Pads,Lights | Saddle,Lights | Saddle,Lights | Saddle)
2,Saddle,0,0,0,0,0,0,1,5,1,5,1,5,Non Buyer,Non Buyer,Non Buyer,New Buyer,2yrs consecutive,3yrs consecutive,,,,Saddle,Saddle,Saddle)
2,All Purchases,0,0,0,0,0,0,1,5,1,5,1,5,Non Buyer,Non Buyer,Non Buyer,New Buyer,2yrs consecutive,3yrs consecutive,,,,Saddle,Saddle,Saddle)
3,Lights,1,5,1,2,1,5,1,2,1,3,0,0,Reactivated Buyer,2yrs consecutive,3yrs consecutive,4yrs consecutive,5+yrs consecutive,Lapsed Buyer,Lights,Lights,Lights,Lights,Lights,)
3,All Purchases,1,5,1,2,1,5,1,2,1,3,0,0,Reactivated Buyer,2yrs consecutive,3yrs consecutive,4yrs consecutive,5+yrs consecutive,Lapsed Buyer,Lights,Lights,Lights,Lights,Lights,)
];
Years:
LOAD * INLINE
[
Year
2005
2006
2007
2008
2009
2010
];
And what I am hoping to be able to do is to link / map related year fields to their respective year. To give a bit mroe detail I am wanting to be able to have 2 list boxes, one for year and one for purchase_type and then to have charts showing data relating to those years.
The issue I am struggling with is to find a way of mapping columns count2005,value2005,loyalty2005,purchases2005 to the year 2005 so that when I select the year filter = '2005' only those columns are used in charts. I have added a variable for the years but am not sure how to map 2005 onto all 4 related fields without creating 4 separate calendars (which seems like overkill to me!).
I am ideally hoping to be able to create a chart where I can have purchase type and year as listbox filters, and then show a stacked total of loyalty or purchases, with the expressions being either distinct count of contact_id / total value for reference year / total count of purchases for reference year - with the charts looking something along these lines - chart
Massive thanks in advance!
Davin
hi,
please see the chart in the attached qvw. To toggle between the count and the value use the icon in the bottom left hand corner.
It's not really magic but qlikview can help you do some neat things to transform your data in the script! Not to be ignored.
basically you now have a table with a record per contact_id, per purchase type, per year. More acceptable from a data model perspective than your original data input.
The code shows the use of a variable the "i" in the for each loop. this takes on respectively the values 2005, thru 2010 and then uses this to firstly create the Year field thus allowing your drop down select and secondly to adress the physical fields loaded initially. very versatile and useful.
the "for each" loop which allows you to save on redundant code, ie repaet the same code just changing the variable each time.
the tmpTable needs creating outside the loop so we can then adress it with each loop and simply add on the rows. this is done with a dummy row having purchase type, contact_id. After the loop we remove the dummy row.
we get rid of all temporary tables otherwise qv will start complaining.
if you are happy hit the verify answer button and I'll be happy too.
enjoy qlikview!
hi,
add the follwoingcode after your two load commands.
this wil create a data model giving you the drop down selects you require. Afterwards I am not quite sure of what your charts are trying to achieve but this should get you closer.
tmpTable:
load
contact_id,
[purchase type]
resident Data;
for each i in 2005, 2006, 2007, 2008, 2009, 2010
tmpTable:
concatenate (tmpTable) load
contact_id,
[purchase type],
$(i) as Year,
count$(i) as fctCount,
value$(i) as fctValue,
loyalty$(i) as loyalty,
replace(purchases$(i),')','') as Purchases
resident Data;
next i
drop table Data;
FinalData:
noconcatenate load
*
resident tmpTable
where len(Year)>1;
drop table tmpTable;
let us know how you get on.
Hi PatAgen,
Thanks for posting - unfortunately I get the following errors when I try your script:
Table not found tmpTable
And then when it tries to do the concatenation none of the tables 2005-2010 are found either? Which then means that the drop tables also can't be done.
Thanks again
hi,
check the attached qvw. the code works ok.
Hi,
I think this script may solve ur problem...
:
Datatemp:
LOAD
* INLINE[contact_id,purchase type,count2005,value2005,count2006,value2006,count2007,value2007,count2008,value2008,count2009,value2009,count2010,value2010,loyalty2005,loyalty2006,loyalty2007,loyalty2008,loyalty2009,loyalty2010,purchases2005,purchases2006,purchases2007,purchases2008,purchases2009,purchases2010
;
Data:
load
2005
,
contact_id
,
count2005
as count,
loyalty2005
as loyalty,
[purchase type]
,
purchases2005
as purchases,
value2005
asvalue
Resident
Datatemp;
Concatenate
load2006
,
contact_id
,
count2006
as count,
loyalty2006
as loyalty,
[purchase type]
,
purchases2006
as purchases,
value2006
asvalue
Resident
Datatemp;
Concatenate
load2007
,
contact_id
,
count2007
as count,
loyalty2007
as loyalty,
[purchase type]
,
purchases2007
as purchases,
value2007
asvalue
Resident
Datatemp;
Concatenate
load2008
,
contact_id
,
count2008
as count,
loyalty2008
as loyalty,
[purchase type]
,
purchases2008
as purchases,
value2008
asvalue
Resident
Datatemp;
Concatenate
load
2009
,
contact_id
,
count2009
as count,
loyalty2009
as loyalty,
[purchase type]
,
purchases2009
as purchases,
value2009
asvalue
Resident
Datatemp;
Concatenate
load2010
,
contact_id
,
count2010
as count,
loyalty2010
as loyalty,
[purchase type]
,
purchases2010
as purchases,
value2010
asvalue
Resident
Datatemp;
drop
tableDatatemp;
@PatAgen Thanks for that - no idea why it didn't work when I copied the data in on top!
I'm still trying to get my head around how this works but it looks like it is doing the grouping as I was hoping would be possible - would it be possible to get an idea of the magic behind this script (as I've only been using qlikview for a few hours now!)
In terms of what I was hoping to be able to do in a bar chart was similar to what is in your SumfctCount straight table (with the columns purchase type / year but to instead only have one column per year, with the purchase type stacked and then ideally have the columns sum(fctCount) and sum(fctValue) as options in a list box, so that if you clicked on sm(fctCount) the bar chart would show a stacked bar for each year selected - for example if 2005 all purchases was selected along with the sum(fctCount) option, there would be one bar with 5 sections (all purchases,bike frame,brake pads,lights,saddle) and the numbers for each section would be (12,0,0,2,10) respectively?
Again, thanks for all your help J
hi,
please see the chart in the attached qvw. To toggle between the count and the value use the icon in the bottom left hand corner.
It's not really magic but qlikview can help you do some neat things to transform your data in the script! Not to be ignored.
basically you now have a table with a record per contact_id, per purchase type, per year. More acceptable from a data model perspective than your original data input.
The code shows the use of a variable the "i" in the for each loop. this takes on respectively the values 2005, thru 2010 and then uses this to firstly create the Year field thus allowing your drop down select and secondly to adress the physical fields loaded initially. very versatile and useful.
the "for each" loop which allows you to save on redundant code, ie repaet the same code just changing the variable each time.
the tmpTable needs creating outside the loop so we can then adress it with each loop and simply add on the rows. this is done with a dummy row having purchase type, contact_id. After the loop we remove the dummy row.
we get rid of all temporary tables otherwise qv will start complaining.
if you are happy hit the verify answer button and I'll be happy too.
enjoy qlikview!