topic Re: How do I write a script for this...? in QlikView

How do I write a script for this...?

Anonymous — Tue, 11 Mar 2014 02:14:56 GMT

I have two fields as below, and I'm trying to add a Field3 to identify the nature of Field1 and Field2. Field1 is the original product list, when Field1 is ' ', we look at Field2 to decide what to put in Field3.

Field1 Field2

'apple' ' '

'peach' ' '

'strawberry' ' '

'greenbean' ' '

' ' 'cabbage'

'wheat' ' '

'tomato'

'broccoli' 'broccoli'

'corn' ' '

: :

. .

then, I wanna add a Field3,

1.when Field1='apple','peach','strawberry' then Field3=fruit;

2.when Field1='greenbean','cabbage','broccoli','tomato' then Field3=veggie;

3. when Field1='wheat','corn' then Field3=grain.

4. when Field1=' ', go see Field2, follow the same rule.

So the completed table is like,

Field1 Field2 Field3

'apple' ' ' fruit

'peach' ' ' fruit

'strawberry' ' ' fruit

'greenbean' ' ' veggie

' ' 'cabbage' veggie

'wheat' ' ' grain

'tomato' veggie

'broccoli' 'broccoli' veggie

'corn' ' ' grain

: : :

. . .

Thanks, guys~!

Re: How do I write a script for this...?

Tue, 11 Mar 2014 02:32:51 GMT

try using IF Function

instead of WHEN

regards

Gernan

Re: How do I write a script for this...?

Tue, 11 Mar 2014 02:41:51 GMT

Hi Lynn,

You can try something similar to this if you have a small number of conditions as in your example above:

TableName:

LOAD

Field1,

Field2,

If( MixMatch(If(Field1<>Null(), Field1, Field2),'APPLE','PEACH','STRAWBERRY'), 'FRUIT',

If( MixMatch(If(Field1<>Null(), Field1, Field2),'greenbean','cabbage','broccoli','tomato'), Veggie,

'Grain')) as FoodType

FROM

DataSource;

Re: How do I write a script for this...?

Anonymous — Tue, 11 Mar 2014 02:50:04 GMT

Thanks, Wes. But what if Field1 is never a Null, it's always ' ' instead of Null...?

Re: How do I write a script for this...?

Tue, 11 Mar 2014 02:53:07 GMT

You can check for the space in your IF statement:

if(Field1<>' ', Field1, Field2)

Re: How do I write a script for this...?

Anonymous — Tue, 11 Mar 2014 02:54:19 GMT

if you have a list of fruits, veggies, grains in a separate table, then you can use apply map too -

so mapping load of name, type fields from the list table (apple, fruit; wheat, grain; etc.) then

map1:

Mapping LOAD * Inline [

name, type

apple, fruit

banana, fruit

peach, fruit

broccoli, veggie

cabbage, veggie

tomato, veggie

wheat, grain

corn, grain

];

base1:

load *,

ApplyMap('map1', if(len(Field2)>0, Field2, Field1)) as F3

Resident base;

where "base" is the first table in your example with Field1 and Field2. If you have a space as value (' '), then you can use 1 instead of 0 in the len comparison.

Re: How do I write a script for this...?

Anonymous — Tue, 11 Mar 2014 03:04:52 GMT

Hi Lynn,

You can use a mapping table and create all your lookup values like this:

mapMasterList:

Mapping Load * Inline [

Input, Output

apple, fruit

peach, fruit

strawberry, fruit

greenbean, veggie

cabbage, veggie

wheat, grain

tomato, veggie

broccoli, veggie

corn, grain

];

Load

Field1,

Field2,

Applymap('mapMasterList', if(len(PurgeChar(Field1,chr(32)))=0, Field2, Field1), 'NA') as Field3

From XYZ;

The Applymap function will see if we remove all spaces from Field1, is it longer than 0 characters? If not, then use Field2. If Field1 actually has a value, use Field1. This value is then mapped against the mapping table to return the output value for Field3. If no match is found, the Applymap function will return 'NA'.