topic Re: Can values be hard-coded into a LOAD statement? in QlikView

Can values be hard-coded into a LOAD statement?

Thu, 13 Jun 2013 14:54:37 GMT

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

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

Anonymous — Thu, 13 Jun 2013 14:58:06 GMT

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?

shree909 — Thu, 13 Jun 2013 14:59:53 GMT

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?

Anonymous — Thu, 13 Jun 2013 15:09:12 GMT

Also true. If it is possible to come up with some set of rules for converting values, it is better than hardcoding or using external files.

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

Thu, 13 Jun 2013 15:14:57 GMT

Thanks to both who responded.

The INLINE option is what I was looking for, it will eliminate reliance on additional Excel files.

The use of IF statements is an option would have made my main LOAD statement much longer. Using this method I can hide the mappings away in another tab.

I know this may not be the best way of doing things, but as I say, I’m new to this and I’m experimenting to find out what works for me.

Thanks again!

Steve

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

Thu, 13 Jun 2013 15:20:08 GMT

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?

Thu, 13 Jun 2013 15:26:45 GMT

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?

Thu, 13 Jun 2013 16:33:11 GMT

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