topic Re: Rename a field in a Script with if in QlikView

Rename a field in a Script with if

Mon, 24 Feb 2014 10:34:35 GMT

Hi,

I'ld like to rename a field in my script like that :

LOAD

Sta as [Sta Field];

IF(Sta= 'A' , 'Active',

IF(Sta= 'B', 'Beginning',

IF(Sta= 'C', 'Close')));

SQL SELECT Sta

FROM TABLE;

But that's didn't work. Can you give me some advice pliz ?

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 10:41:41 GMT

Hi try like this,

Load

Sta as [sta Field];

if(match(sta='A','Active',

IF(match(Sta= 'B', 'Beginning',

IF(match(Sta= 'C', 'Close')));

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 10:42:50 GMT

Hi Erwan

First of all remove semicolon after [Sta Field]

Sta as [Sta Field];

Then put some column name for the column with If statements

IF(Sta= 'C', 'Close'))) AS [Sta Name];

You also may like you use mapping table/applymap for this instead of nested If

Lukasz

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 10:51:54 GMT

Erwan,

Do you want to rename field or field value ?

If it is the value, you can use ApplyMap() function.

First you load a 2 field table with the Mapping Load statement. You will have A - Active, B - Beginning ...

Second, you load your table & your field :

LOAD .... applymap('MapTable', Value) as XXXX

(the value is A, B, C : the first column of the mapping table)

Fabrice

Re: Rename a field in a Script with if

nizamsha — Mon, 24 Feb 2014 11:00:18 GMT

Just as an Example

TableA:

LOAD * Inline [

Sta

];

LOAD Sta as Sta1 ,if(Sta='A','Active',IF(Sta='B','Begining',IF(Sta='C','Cancel'))) as Sta2 Resident TableA;

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 11:09:52 GMT

Thx for your help. You are right, I want to rename/change my value. I'll try the applymap function.

In my list, I've this :

Sta :

And I want this :

Active
Beginning
Close

Btw I'm a beginner on QlikView and i'm not very proud of my English.

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 11:15:21 GMT

Erwan,

Map1:

Mapping LOAD * INLINE [

Colkey, ColReplace

A, Active

B, Beginning

C, Close]

(the names of the columns do not matter)

Afterwards,

Load .....,

ApplyMap('Map1', STA) as xxx

.....

Assuming that STA is A, B or C. If sth else, Applymap will return null() except if you set a 3d argument, sth like 'STA not found'.

Fabrice

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 11:16:54 GMT

Hi Erwan,

Just replace the semi colon with comma, after the [Sta Field] and then try it. It should work.

corrected script

LOAD

Sta as [Sta Field],

IF(Sta= 'A' , 'Active',

IF(Sta= 'B', 'Beginning',

IF(Sta= 'C', 'Close')));

SQL SELECT Sta

FROM TABLE;

Re: Rename a field in a Script with if

Mon, 24 Feb 2014 11:35:37 GMT

Work great!

Thank you a lot Fabrice.