topic Re: Rename value with multiple names in QlikView

Rename value with multiple names

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Rename value with multiple names

sunny_talwar — Tue, 18 Oct 2016 10:49:11 GMT

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

Re: Rename value with multiple names

jessica_webb — Tue, 18 Oct 2016 10:57:14 GMT

Perfect solution!

Thanks Sunny

Re: Rename value with multiple names

jessica_webb — Tue, 18 Oct 2016 11:39:17 GMT

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

Re: Rename value with multiple names

sunny_talwar — Tue, 18 Oct 2016 11:47:27 GMT

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?

Re: Rename value with multiple names

sunny_talwar — Tue, 18 Oct 2016 11:51:44 GMT

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);

Re: Rename value with multiple names

jessica_webb — Tue, 18 Oct 2016 11:52:58 GMT

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.

Re: Rename value with multiple names

sunny_talwar — Tue, 18 Oct 2016 11:56:54 GMT

So get a table like this in QlikView?

Re: Rename value with multiple names

jessica_webb — Tue, 18 Oct 2016 12:08:47 GMT

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

Re: Rename value with multiple names

sunny_talwar — Tue, 18 Oct 2016 12:09:53 GMT

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

Re: Rename value with multiple names

jessica_webb — Tue, 18 Oct 2016 12:28:30 GMT

Thank you so much!

Exactly what I needed (and have tested and re-tested so I won't be coming back to ask more questions!).

Thanks again,

Jess

Re: Rename value with multiple names

sunny_talwar — Tue, 18 Oct 2016 12:29:16 GMT

Hahahaha always good to be certain