7 Replies Latest reply: Jun 13, 2013 12:33 PM by Dagg Stephen RSS

    Can values be hard-coded into a LOAD statement?

      I'm pretty new to Qlikview scripting. I want to add in a number of mapping tables, since the values in my data source are a bit long winded and take up a lot of screen space.

       

      e.g, I want to replace "1 to 3 days a week - Car" with "1-3 day/week",

      "less than 1 day a year (or never)" with "< 1 day/year"

      etc.

       

      However, there are about 10 of these, each with about 5 substitutions needed

       

      Rather than creating a spreadsheet with 10 worksheets, one for each mapping, I'd like to embed them in the Load statement somehow. This should make it easy for me to copy and paste the definitions - as you can see there's a lot of reptition in there.

       

      Can anyone suggest a syntax for the LOAD ststement to generate a mapping table without a data source? Or is there a better way of achieving this?

       

      Many thanks

       

      Steve

       

      ImportanceSatisfactionGraph.PNG

        • Re: Can values be hard-coded into a LOAD statement?
          Michael Solomovich

          Values hardcoded in script - yes, use load inline.

           

          Edit: A better way maybe to use Excel file for mapping, depending on your situation.

          • Re: Can values be hard-coded into a LOAD statement?
            sree anaarasi

            Hi

            u can always do it in the script using functions lik Apply Map

            IF and replace function

             

            if( fieldname ='1 to 3 days a week - Car','1-3 day/week',

            if (fieldname =...)

             

            somehting like this..

            • Re: Can values be hard-coded into a LOAD statement?

              You can create an inline mapping table containing all your conversions:

               

              overall_map:

              MAPPING LOAD * INLINE [

                  long, short

                  1 to 3 days a week - Car, 1-3 day/week

                  less than 1 day a year (or never), < 1 day/year

                  1 to 3 days a week - Walk, 1-3 day/week

                  1 to 3 days a week - Metro, 1-3 day/week

              ];

               

               

              And then you can use the same apply map on all load statements (like Metro, Car, Walk). Like that you don't need to duplicate the mappings.

               

               

              Metro:

              Load

                value as orig_value,

                ApplyMap('overall_map', value, value) as mapped_value

              INLINE [

                  value

                  1 to 3 days a week - Metro

                  less than 1 day a year (or never)

              ];

               

               

              Walk:

              Load

                value as orig_value,

                ApplyMap('overall_map', value, value) as mapped_value

                INLINE [

                  value

                  1 to 3 days a week - Walk

                  less than 1 day a year (or never)

              ];

               

               

              Car:

              Load

              value as orig_value,

                ApplyMap('overall_map', value, value) as mapped_value

              INLINE [

                  value

                  1 to 3 days a week - Car

                  less than 1 day a year (or never)

              ];

                • Re: Can values be hard-coded into a LOAD statement?

                  Very interesting Marcel, I will study this and try to get to grips with this technique.

                  Thanks

                  Steve

                    • Re: Can values be hard-coded into a LOAD statement?

                      I now have this working. I added the following code to a new sheet called "Mappings":

                       

                       

                       

                      UsageMapping:

                      mapping LOAD

                      * INLINE [
                          longText, shortText
                          '4+ days a week - Car',       '1) 4+ day/week'   
                          '1 to 3 days a week - Car',      '2) 1-3 day/week'
                          'Less than 1 day a week - Car',     '3) <1 day/week'
                          'Less than 1 day a month - Car',    '4) <1 day/month'
                          'Less than 1 day a year (or never) - Car',  '5) <1 day/year'

                          '4+ days a week - Metro',      '1) 4+ day/week'   
                          '1 to 3 days a week - Metro',     '2) 1-3 day/week'
                          'Less than 1 day a week - Metro',    '3) <1 day/week'
                          'Less than 1 day a month - Metro',    '4) <1 day/month'
                          'Less than 1 day a year (or never) - Metro', '5) <1 day/year'

                          '4+ days a week - Bus',       '1) 4+ day/week'   
                          '1 to 3 days a week - Bus',      '2) 1-3 day/week'
                          'Less than 1 day a week - Bus',     '3) <1 day/week'
                          'Less than 1 day a month - Bus',    '4) <1 day/month'
                          'Less than 1 day a year (or never) - Bus',  '5) <1 day/year'

                          '4+ days a week - Walk',      '1) 4+ day/week'   
                          '1 to 3 days a week - Walk',     '2) 1-3 day/week'
                          'Less than 1 day a week - Walk',    '3) <1 day/week'
                          'Less than 1 day a month - Walk',    '4) <1 day/month'
                          'Less than 1 day a year (or never) - Walk',  '5) <1 day/year'

                          '4+ days a week - Other',      '1) 4+ day/week'   
                          '1 to 3 days a week - Other',     '2) 1-3 day/week'
                          'Less than 1 day a week - Other',    '3) <1 day/week'
                          'Less than 1 day a month - Other',    '4) <1 day/month'
                          'Less than 1 day a year (or never) - Other', '5) <1 day/year'

                      ];

                       

                       

                      Then adusted my main LoAD statement

                       

                       

                       

                       

                      ApplyMap('UsageMapping', [1.2.1) Bus usage]) as [Bus usage],
                      ApplyMap('UsageMapping', [1.2.1) Car usage]) as [Car usage],
                      ApplyMap('UsageMapping', [1.2.1) Metro usage]) as [Metro usage],
                      ApplyMap('UsageMapping', [1.2.1) Walk usage]) as [Walk usage],
                      ApplyMap('UsageMapping', [1.2.1) Other usage]) as [Other usage]

                      ,

                      dashboard now looks much neater, ready to have list boxes shrunk. Will repeat process for "change type" fields

                       

                      Thanks for everyone's help

                       

                       

                       

                      ImportanceSatisfactionGraph.PNG