Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Values hardcoded in script - yes, use load inline.
Edit: A better way maybe to use Excel file for mapping, depending on your situation.
Values hardcoded in script - yes, use load inline.
Edit: A better way maybe to use Excel file for mapping, depending on your situation.
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..
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.
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
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)
];
Very interesting Marcel, I will study this and try to get to grips with this technique.
Thanks
Steve
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