topic Re: Creating new field and values in script in QlikView

Creating new field and values in script

jessica_webb — Fri, 16 Oct 2015 13:54:49 GMT

Hi,

I'm sure this must be really straightforward, but I can't find a solution anywhere!

I have a field with lots of different values, and I want to use these values to create a new field, with only 3 values, in the script.

E.g.

Temperature

100

I want a new field called 'Description' that gives the value 'Freezing' if the Temp is = 0, 'Boiling' if the Temp is = 100, and 'Between' if the the Temp is neither 0 or 100.

Thanks,
Jess

Re: Creating new field and values in script

sunny_talwar — Fri, 16 Oct 2015 14:03:30 GMT

Try this:

Table:

LOAD *,

Pick(Match(Temperature, 0, 100, Temperature), 'Freezing', 'Boiling', 'Between') as Description;

LOAD * Inline [

Temperature

100

];

Re: Creating new field and values in script

pokassov — Fri, 16 Oct 2015 14:04:46 GMT

hi!

load

Temperature,

if(Temperature=0,'Freezing',

if(Temperature=100,'Temp=100', 'Between')) as Temp

from ....

Re: Creating new field and values in script

vishsaggi — Fri, 16 Oct 2015 14:07:37 GMT

Hi,

Temprature:

LOAD *,

If(Temperature = 0 , 'Freezing',

IF(Temperature = 100, 'Boiling',

If(Temperature >0 AND Temperature < 100, 'Between'))) As Description;

Load * Inline [

Temperature

100

];

Thanks,

Re: Creating new field and values in script

Gabriel — Fri, 16 Oct 2015 14:11:02 GMT

Hi,

I will advocate for using PICK() and MATCH() Function simply for load performance.

Try this:-

Temperature_Data:

LOAD *

,PICK(MATCH(Temperature, 0 , 100 , Temperature),'Freezing','Boilong','Between') AS Description

;

LOAD * INLINE [

Temperature

100

];

EDITED because I notice this same suggestion above

Re: Creating new field and values in script

nagaiank — Fri, 16 Oct 2015 14:22:41 GMT

You may use the following script in order to take care of temperatures other than 0 and 100 correctly.

Temprature:

LOAD *,

If(Temperature <= 0 , 'Freezing',

IF(Temperature >= 100, 'Boiling',

If(Temperature >0 AND Temperature < 100, 'Between'))) As Description;

Load * Inline [

Temperature

100

];

Re: Creating new field and values in script

jessica_webb — Fri, 16 Oct 2015 14:25:47 GMT

Hi,

Thanks for everyone's responses.

This solution is perfect (I should have mentioned that the list of different temperatures was a lot longer than my example, so an inline table wouldn't work).

Thanks again,

Jess