topic Re: Rename two identical values in the same field in QlikView

Rename two identical values in the same field

Wed, 15 Oct 2014 18:21:19 GMT

Hi everyone,

I have the following example:

Food	Type
Fruit	Orange
Drink	Orange

I want to rename the two 'Orange' values in the 'Type' Field differently, based on the associated values in the 'Food' Field. Does anyone know how I can do this in the script? I need help with both the method, and the syntax. I think I could use REPLACE or MAPPING, I just don't know how I would type it out.

Thanks!

Re: Rename two identical values in the same field

maxgro — Wed, 15 Oct 2014 18:33:17 GMT

tmp:

load * inline [

Food, Type

Fruit, Orange

Drink, Orange

Fruit, Lemon

];

final:

NoConcatenate load

Food,

if(Type = 'Orange',

pick(match(Food, 'Fruit', 'Drink'), 'Orange Fruit', 'Orange Drink'),

Type) as Type

Resident

tmp;

DROP Table tmp;

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:04:52 GMT

Massimo,

Thank you so much! Unfortunately, this generated a synthetic key - perhaps I should have mentioned that the two fields 'Food' and 'Type', and the 'Orange' Type values, are in a pre-existing data table. So perhaps I should use your If pick(match) syntax in a different way?

Re: Rename two identical values in the same field

maxgro — Wed, 15 Oct 2014 19:09:50 GMT

I think you can replace my tmp table with your preexisting one

Or post the script, maybe I don't understand

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:11:03 GMT

Hi Andrew,

Can you attach the model? may you can try in the script load something like this:

Load

Food,

Food & ' ' & Type AS Type

From table

so when Qlik reads Food = drink and Type = Orange, you will have Food =drink, Type = drink Orange

Hope it helps!

Regards

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:27:56 GMT

My script looks something like:

Systems:

LOAD

Account,

Amounts,

[Market Sector],

[Market Segment],

[Platform Type],

[Platform Category],

[Platform Sub-Category],

[Customer],

FROM

[\\xx\xx\xx.qvd]

So my question is, I have the value 'Orange' in [Platform Category] - one for 'Fruit' in [Platform Type] and one for 'Drink' in [Platform Type]. How can I edit the script to conditionally change 'Orange' to 'Orange Fruit' and 'Orange Drink'? I only want to do that change for Orange, not for all other values in [Platform Category].

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:30:35 GMT

Try this:

Systems:

LOAD

Account,

Amounts,

[Market Sector],

[Market Segment],

[Platform Type],

IF ( [Platform Category] = 'Orange' ,

[Platform Type] & ' ' & [Platform Category],

[Platform Category] )

AS [Platform Category],

[Platform Sub-Category],

[Customer],

FROM

[\\xx\xx\xx.qvd]

You will see the difference in [Platform Category] directly.

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:37:47 GMT

Jon,

This worked, but I only want the & ' ' & replacement to occur when the Platform Category = Orange. Do you know how I can expand your statement to be conditional?

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:40:16 GMT

I add an If sentence in the previos reply, here is again:

Systems:

LOAD

Account,

Amounts,

[Market Sector],

[Market Segment],

[Platform Type],

IF ( [Platform Category] = 'Orange' ,

[Platform Type] & ' ' & [Platform Category],

[Platform Category] )

AS [Platform Category],

[Platform Sub-Category],

[Customer],

FROM

[\\xx\xx\xx.qvd]

You will see the difference in [Platform Category] directly.

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:42:24 GMT

Dont forget to mark the post as the right answer if it helps you please

Re: Rename two identical values in the same field

Wed, 15 Oct 2014 19:46:48 GMT

YES it worked!! Thank you Jon! And thank you Massimo, as well!