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]

Can you share the script for the problem area?

[jessica_webb]

Please see my last reply to Stefan where I outline the 'JOIN' tables that I'm trying to use.

[swuehl]

This seems not to be related to the new Dim table at all, right?

When you are saying, 'following the LEFT JOIN table, I now get error 'Field not found - <Total>', this means the LEFT JOIN is working without any issues, but then there is an error on a following LOAD statement?

Have you created an exit script statement after the working part and checked your data model / tables?

[jessica_webb]

Hi,

Sorry - getting out of my depth now, so might just have to give up.

Yes, the issue that I'm now having is not related to the new Dim table per se. However, the issue wasn't there before I put the new Dim table in.

I'm not sure about the exit script statement, however I have 'commented out' everything up to the point where it works (which I guess is the amateur version of 'exit script statement'?).

My data model now has three tables:

The original 'Fact' table

The new 'Dim' table

Plus a synthetic table

'Total' can be seen on the new Dim table, but not on the other two.

If any of this makes sense to anyone, then suggestions appreciated. Otherwise, I might have to go back to the drawing board.

[sunny_talwar]

If you are joining the Total field to Facts, why are you seeing it in Dim table. Can you make sure the Join is to Facts? (Remove the Colon next to Facts and put it in parenthesis

JOIN (Facts)

LOAD Groceries,

     Total

FROM

[****]

(ooxml, embedded labels, table is Sheet1);

[jessica_webb]

Hi Sunny,

Removing the colon helped with that error, and now I think I'm beginning to understand where I'm going wrong...

So, I now have two tables (plus a synthetic table):

Facts

Shop_ID,

Groceries,

Grocery_subcat

Dim

Type

Total

The problem is, all of my later calculations rely on this being just one table.

E.g.

LEFT JOIN (Facts)

LOAD

Type,

Sum(Total)/Count(Shop_ID) as Costings_per

Resident Facts

Group by Type;

So, 'Type' being in a different table means my calculations aren't working.

[sunny_talwar]

So I guess the only option, I see is to Join Dim to Facts and create multiple rows for rows which include Apple and Oranges. As you combine them in one table, you would be able to perform your left join correctly I guess

[jessica_webb]

I think you might be right. The data files I'm working with aren't too large, so hopefully it won't cause too many issues.

Would I just join it in the same way I've been doing with my calculations?

[sunny_talwar]

Ya, just like that

Join(FACTS)

LOAD * INLINE [

Groceries, Type

Apples, Food

Oranges, Food

Apples, Fruit

Oranges, Fruit

Bread, Food

Cereal, Food

Water, Drink

Coffee, Drink

];

Related to the potential issues

1 issue I can think of is that if you have lets say sales as one of the columns. It will duplicate and then you won't be able to use Sum(Sales) because Sum(Sales) will double for Apples and Oranges. You will have to use Aggr() function to make sure you pick the sales only once.

[jessica_webb]

Thanks Sunny, will give that a go. And thanks for the insight into potential issues - wouldn't have occurred to me for quite a while!