Rename value with multiple names

[jessica_webb]

Hello,

This might be a bit of a strange thing to want to do, but hoping someone can help me find a solution.

I have a large dataset, with a field called 'Food'. There are hundreds of different values in this field, but one in particular called 'Fruit'.

What I would like to do, is rename the value 'Fruit', but with lots of different names:

- Apples

- Pears

- Bananas

- Mangos

- Raspberries

- Kiwis

So, if I created a list box for 'Food', among the other values I would have the 6 fruits above listed (but not the original value 'Fruit').

I would like each of these new values to behave in the same way, and have the same associations as the original value.

Anyone have any ideas?

Many thanks,

Jess

[sunny_talwar]

Create a link table

LinkTable:

LOAD Food,

           Food as NewFood

Resident FactTable

Where Food <> 'Fuit';

Concatenate (LinkTable)

LOAD * INLINE [

Food, NewFood

Fruit, Apples

Fruit, Pears

Fruit, Bananas

Fruit, Mangos

Fruit, Raspberries

Fruit, Kiwis

];

Now you can use NewFood instead of Food to view all the fruits

[jessica_webb]

Perfect solution!

Thanks Sunny

[jessica_webb]

Sorry - got a bit over-excited! It's actually not quite as I need it (my fault for not explaining properly).

My original table looks like this:

Place_ID	Food	Subcat_food	Price	Cat
6	Pizza		1	Frozen
3	Chips		1.5	Frozen
6	Fruit	Colour	1.35	Fresh
3	Fruit	Colour	1.45	Fresh

My other table has the following:

Place_ID	Fruit	Colour
6	Apple	Red
6	Apple	Green
6	Pear	Brown
6	Pear	Gold
6	Berry	Red
6	Berry	Blue
6	Berry	Green
6	Berry	White

So all I want, is to be able to replace 'Fruit' and 'Colour' with the options in my second table. So if I selected 'Apple' 'Red' it would tell me that it is 1.35 and Fresh.

Does that make any sense?

Thanks!

Jess

[sunny_talwar]

I am not sure I understand. Selecting Apple and Green will give you the same out put also or any other combination from your other table?

[sunny_talwar · Accepted solution]

See if this make sense:

Table:

LOAD Place_ID,

    Food,

    Subcat_food,

    Price,

    Cat

FROM

[https://community.qlik.com/thread/236739]

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD Distinct

  Place_ID,

  Food,

  Food as NewFood

Resident Table

Where Food <> 'Fruit';

Concatenate (LinkTable)

LOAD Place_ID,

  'Fruit' as Food,

    Fruit as NewFood

FROM

[https://community.qlik.com/thread/236739]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (LinkTable)

LOAD Place_ID,

    Fruit as NewFood,

    Colour

FROM

[https://community.qlik.com/thread/236739]

(html, codepage is 1252, embedded labels, table is @2);

[jessica_webb]

Exactly. So I want the user to be able to select any of the fruit/colour combinations - in actual fact it'll just be a duplicate of the entire row in the original table.

It should basically be the same end result as if I copied the fruit/colour row in my original data file however many times, and just replaced the values with those in table two.

[sunny_talwar]

So get a table like this in QlikView?

[jessica_webb]

Yep, that looks right. Is that the code below?

[sunny_talwar]

Yes, the script and a sample can be found with my response underneath