6 Replies Latest reply: Apr 21, 2011 9:33 AM by Patrick Laredo RSS

    Mapping multiple data columns to the same field

      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
          Patrick Laredo

          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

              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
                  Patrick Laredo

                  hi,

                  check the attached qvw. the code works ok.

                    • Mapping multiple data columns to the same field

                      @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
                          Patrick Laredo

                          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. Yes

                          enjoy qlikview!

                  • Mapping multiple data columns to the same field

                    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

                     

                     

                    as

                    value

                    Resident

                     

                     

                    Datatemp;

                    Concatenate

                     

                     

                    load

                     

                    2006



                     

                    as Year

                    ,

                    contact_id

                     

                     

                    ,

                    count2006

                     

                     

                    as count

                    ,

                    loyalty2006

                     

                     

                    as loyalty

                    ,

                    [purchase type]

                     

                     

                    ,

                    purchases2006

                     

                     

                    as purchases

                    ,

                    value2006

                     

                     

                    as

                    value

                    Resident

                     

                     

                    Datatemp;

                    Concatenate

                     

                     

                    load

                     

                    2007



                     

                    as Year

                    ,

                    contact_id

                     

                     

                    ,

                    count2007

                     

                     

                    as count

                    ,

                    loyalty2007

                     

                     

                    as loyalty

                    ,

                    [purchase type]

                     

                     

                    ,

                    purchases2007

                     

                     

                    as purchases

                    ,

                    value2007

                     

                     

                    as

                    value

                    Resident

                     

                     

                    Datatemp;

                    Concatenate

                     

                     

                    load

                     

                    2008



                     

                    as Year

                    ,

                    contact_id

                     

                     

                    ,

                    count2008

                     

                     

                    as count

                    ,

                    loyalty2008

                     

                     

                    as loyalty

                    ,

                    [purchase type]

                     

                     

                    ,

                    purchases2008

                     

                     

                    as purchases

                    ,

                    value2008

                     

                     

                    as

                    value

                    Resident

                     

                     

                    Datatemp;

                    Concatenate

                    load

                     

                     

                     

                    2009



                     

                    as Year

                    ,

                    contact_id

                     

                     

                    ,

                    count2009

                     

                     

                    as count

                    ,

                    loyalty2009

                     

                     

                    as loyalty

                    ,

                    [purchase type]

                     

                     

                    ,

                    purchases2009

                     

                     

                    as purchases

                    ,

                    value2009

                     

                     

                    as

                    value

                    Resident

                     

                     

                    Datatemp;

                    Concatenate

                     

                     

                    load

                     

                    2010



                     

                    as Year

                    ,

                    contact_id

                     

                     

                    ,

                    count2010

                     

                     

                    as count

                    ,

                    loyalty2010

                     

                     

                    as loyalty

                    ,

                    [purchase type]

                     

                     

                    ,

                    purchases2010

                     

                     

                    as purchases

                    ,

                    value2010

                     

                     

                    as

                    value

                    Resident

                     

                     

                    Datatemp;

                    drop

                     

                     

                    table

                    Datatemp;