Creating new field with value overlap

[jessica_webb]

Hi,

Apologies for the confusing discussion title, but I'm not sure how to explain what I need!

Basically, I have a field named 'Groceries', with the following values:

- Apples

- Oranges

- Bread

- Cereal

- Water

- Coffee

I would like to create a new field, named 'Type', as follows:

If(Wildmatch(Groceries,'Apples', 'Oranges', 'Bread', 'Cereal'), 'Food', If(Wildmatch(Groceries,'Water', 'Coffee'), 'Drink')) as Type,

which works fine.

However, within 'Type', I would like a third category called 'Fruit', which includes 'Apples' and 'Oranges'. This doesn't work as QV doesn't seem to like a value being assigned to two different categories within one field.

Does anyone have any suggestions?

Many thanks,
Jess

[sunny_talwar]

How would you see those rows with Apples and Oranges? Multiple rows one saying type as Food and other calling it as Fruit or would you want to see Food and Fruit?

[swuehl]

If I understood correctly, you would need to create multiple records for each Groceries with multiple Type.

Something along these lines:

Facts:

LOAD * INLINE [

Groceries

Apples

Oranges

Bread

Cereal

Water

Coffee

];

Dim:

LOAD DISTINCT

*

,If(Wildmatch(Groceries,'Apples', 'Oranges', 'Bread', 'Cereal'), 'Food', If(Wildmatch(Groceries,'Water', 'Coffee'), 'Drink')) as Type

Resident Facts

;

Concatenate (Dim)

LOAD * INLINE [

Groceries, Type

Apples, Fruit

Oranges, Fruit

];

I assumed Facts table shows more fields than listed in this sample, so you don't want to duplicate lines here (like avoiding duplicate sales number). Create a dimensional table with a link to the groceries and a field for type. Here you can duplicate groceries, creating an association to different types.

[jessica_webb]

Hi Sunny,

I'm not really sure... (not very helpful I know).

The purpose of this is so that I can use 'Type' as a dimension for an expression.

I would like to see the expression results against:
Fruit

Food

Drink

But as it's a fairly large dataset, I don't really want duplicate rows either.

[sunny_talwar]

May be try like this:

LOAD Groceries,

          OtherFields

FROM Source;

LinkTable:

LOAD * INLINE [

Groceries, Type

Apples, Food

Oranges, Food

Apples, Fruit

Oranges, Fruit

Bread, Food

Cereal, Food

Water, Drink

Coffee, Drink

];

Now use Type as your dimension

[florentina_doga]

try this script;

rink:

mapping LOAD * inline [x,y

Water,drink

Coffee,drink

];

food:

mapping LOAD * inline [x,y

Apples,food

Oranges,food

Bread,food

Cereal,food

];

fruit:

mapping LOAD * inline [x,y

Apples,fruit

Oranges,fruit

];

aa:

load *,ApplyMap ('drink', product) as tip1;

load *,ApplyMap ('food', product) as tip2;

load *,ApplyMap ('fruit', product) as tip3;

load * inline [product

Apples

Oranges

Bread

Cereal

Water

Coffee];

NoConcatenate

final:

load product,

    tip1 as type

resident aa where tip1='drink';   

concatenate(final)

load product,

    tip2 as type

resident aa where tip2='food';   

concatenate(final)

load product,

    tip3 as type

resident aa where tip3='fruit';   

drop table aa;

[jessica_webb]

Hi Stefan,

This works perfectly in terms of creating the fields etc. Unfortunately, I get an issue later on in the load.

Following the concatenate, I have various table joins with calculations. The first join adds a new field called 'Total' against the 'Groceries' field. However, this new 'Total' field is now no longer recognised  in the load. Any ideas why that would be?

Thanks,
Jess

[swuehl]

Not sure if I understand what you are trying to do.

If you want a total Type value, maybe like

Dim:

LOAD DISTINCT

*

,If(Wildmatch(Groceries,'Apples', 'Oranges', 'Bread', 'Cereal'), 'Food', If(Wildmatch(Groceries,'Water', 'Coffee'), 'Drink')) as Type

Resident Facts

;

Concatenate (Dim)

LOAD  Groceries, 'Total' as Type

Resident Dim;

Same principle, create a dimensional table that shows all associations needed between Type and Groceries.

[jessica_webb]

Sorry - I'll try and explain more clearly.

Your first response gave me the exact outcome I needed. I.e. It allowed me to use 'Type' as a dimension which has 'Fruit', 'Food' and 'Drink' as values (with Apples/Oranges appearing in both 'Fruit' and 'Food').

However, later in the script I carry out variety of calculations which have been added on with JOIN.

So, the first JOIN is:

JOIN [Facts]:

LOAD Groceries,

     Total

FROM

[****]

(ooxml, embedded labels, table is Sheet1);

The next JOIN is:

LEFT JOIN (Facts)

LOAD

Shop_ID,

Groceries,

Grocery_subcat,

Avg((Actual-Estimated)/Total) as [Costings]

Resident Facts

Group by Grocery_subcat, Groceries, Shop_ID;

However, following the LEFT JOIN table, I now get error 'Field not found - <Total>'

It worked before, so I'm wondering why it no longer accepts this.

[jessica_webb]

Hi Sunny,

This works perfectly, but gives me the same problem that I'm having with Stefan's solution - i.e. my later calculations on the 'Groceries' field no longer work.