Rename two identical values in the same field

[#1]

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!

[maxgro]

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; 

[#3]

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?

[maxgro]

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

Or post the script, maybe I don't understand

[#5]

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

[#6]

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

[#7]

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.

[#8]

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?

[#9 · Accepted solution]

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.

[#10]

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