Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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~!

1 Solution

Accepted Solutions
Not applicable
Author

You can check for the space in your IF statement:

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

View solution in original post

6 Replies
Not applicable
Author

hi

try using IF Function

instead of WHEN

regards

Gernan

Not applicable
Author

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;

Anonymous
Not applicable
Author

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

Not applicable
Author

You can check for the space in your IF statement:

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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