topic Mapping multiple data columns to the same field in QlikView

Mapping multiple data columns to the same field

Mon, 26 Jan 2026 18:19:17 GMT

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

Mapping multiple data columns to the same field

pat_agen — Thu, 21 Apr 2011 12:08:12 GMT

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.

Mapping multiple data columns to the same field

Thu, 21 Apr 2011 12:28:22 GMT

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

Mapping multiple data columns to the same field

pat_agen — Thu, 21 Apr 2011 12:35:53 GMT

hi,

check the attached qvw. the code works ok.

Mapping multiple data columns to the same field

Thu, 21 Apr 2011 12:51:20 GMT

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

as Year

contact_id

count2005

as count

loyalty2005

as loyalty

[purchase type]

purchases2005

as purchases

value2005

value

Resident

Datatemp;

Concatenate

load

2006

as Year

contact_id

count2006

as count

loyalty2006

as loyalty

[purchase type]

purchases2006

as purchases

value2006

value

Resident

Datatemp;

Concatenate

load

2007

as Year

contact_id

count2007

as count

loyalty2007

as loyalty

[purchase type]

purchases2007

as purchases

value2007

value

Resident

Datatemp;

Concatenate

load

2008

as Year

contact_id

count2008

as count

loyalty2008

as loyalty

[purchase type]

purchases2008

as purchases

value2008

value

Resident

Datatemp;

Concatenate

load

2009

as Year

contact_id

count2009

as count

loyalty2009

as loyalty

[purchase type]

purchases2009

as purchases

value2009

value

Resident

Datatemp;

Concatenate

load

2010

as Year

contact_id

count2010

as count

loyalty2010

as loyalty

[purchase type]

purchases2010

as purchases

value2010

value

Resident

Datatemp;

drop

table

Datatemp;

Mapping multiple data columns to the same field

Thu, 21 Apr 2011 13:02:43 GMT

@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

Mapping multiple data columns to the same field

pat_agen — Thu, 21 Apr 2011 13:33:07 GMT

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!