topic Re: Renaming Records in a Field in QlikView

Renaming Records in a Field

Wed, 26 Feb 2014 11:33:25 GMT

Hi All,

I have an Excel file with a Column called Measures. This contains the following values:

Units

Actuals

Sell In

Channel Fill

When I create a listbox, these are the selection values that appear.

I need to change the name of some of these values in the listbox. eg.. change Units to 'Demand Units'

Any ideas on how I can do this? Can I rename the values during the script load?

Regards

John

Re: Renaming Records in a Field

sundarakumar — Wed, 26 Feb 2014 11:36:35 GMT

while loading in load script

if(Measures='Units','Demand Units',Measeures) as Measures,

....

from xyz;

_sundar

Re: Renaming Records in a Field

Luis_Cortizo — Wed, 26 Feb 2014 11:41:17 GMT

Hi, John

I presume that in you're script you have something similar to:

Load

[...]

Measures,

[...]

FROM [Your_Excel_File];

The more straightforward way of accomplishing what you want would be a simple If statement. Something like:

Load

[...]

If(Measures='Units', 'Demand Units', Measures) as Measures;

[...]

FROM [Your_Excel_File];

Please find an example with an inline table attached.

Best regards.

Re: Renaming Records in a Field

Wed, 26 Feb 2014 11:43:21 GMT

Hi Sundar,

You can create a inline table in script rather than using if condition.Performancewise it would be ggod.

Re: Renaming Records in a Field

Luis_Cortizo — Wed, 26 Feb 2014 11:48:26 GMT

If that's an option I totally agree with Mahendra

Re: Renaming Records in a Field

maxgro — Wed, 26 Feb 2014 11:48:44 GMT

try with

load

pick(wildmatch(Measures, 'Units', 'Sell In', '*'), 'Demand Units', 'New Sell In', Measures) as Measures;

LOAD * INLINE [

Measures

Units

Actuals

Sell In

Channel Fill

];

Re: Renaming Records in a Field

Clever_Anjos — Wed, 26 Feb 2014 12:14:28 GMT

map:
mapping load * INLINE [
From, To
Units,Demand Units
Actuals,Demand Actuals
Sell In,Sell In
Channel Fill,Channel Fill,
];

LOAD
*,
applymap('map',Measures) as Measures2
from yourqvd(qvd);

Re: Renaming Records in a Field

Wed, 26 Feb 2014 14:43:12 GMT

HI All,

Thanks for all the replies..

I need to change more that one value, how do I amend the following code to do this? I tried afew possibilities but couldn't get it to work - I got a duplicate error.

If(Measures='Units', 'Demand Units', Measures) as Measures;

Thanks so much

John

Re: Renaming Records in a Field

maxgro — Wed, 26 Feb 2014 15:27:38 GMT

perhaps I don't understand your question but you can use my answer or Clever Anjos answer to change more than one value

if you like if you can use nested if

If(Measures='Units', 'Demand Units',

If(Measures='Old', 'New',

If(Measures='Old1', 'New1',

Measures))) as Measures,

or I'm missing something?

Re: Renaming Records in a Field

sundarakumar — Thu, 27 Feb 2014 07:51:28 GMT

I think u have write script as below

load

if(measure='units','demand units',measure) as measures,

if(meseure='name','newname',measure) as measure,

...

form

xyz;

This will result in duplicate error, u should create nested if like

load

if(measure='units','demannd units',if(measure='name','newname',if(measures='name2','newname2',measure))),

...

from xyz;

bold is the innermost if statement

underlined is the last but one

This is how nested if should be written.

hope this helps.

-Sundar