topic Re: Creating new field with value overlap in QlikView

Creating new field with value overlap

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

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

Re: Creating new field with value overlap

sunny_talwar — Wed, 04 May 2016 08:57:43 GMT

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?

Re: Creating new field with value overlap

swuehl — Wed, 04 May 2016 09:35:10 GMT

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.

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 10:14:12 GMT

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.

Re: Creating new field with value overlap

sunny_talwar — Wed, 04 May 2016 10:34:15 GMT

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

Re: Creating new field with value overlap

florentina_doga — Wed, 04 May 2016 10:36:26 GMT

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;

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 10:38:01 GMT

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

Re: Creating new field with value overlap

swuehl — Wed, 04 May 2016 10:42:03 GMT

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.

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 10:49:27 GMT

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.

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 10:50:11 GMT

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.

Re: Creating new field with value overlap

sunny_talwar — Wed, 04 May 2016 10:51:57 GMT

Can you share the script for the problem area?

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 10:53:01 GMT

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

Re: Creating new field with value overlap

swuehl — Wed, 04 May 2016 10:55:01 GMT

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?

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 11:02:18 GMT

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.

Re: Creating new field with value overlap

sunny_talwar — Wed, 04 May 2016 11:05:15 GMT

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

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 12:12:18 GMT

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.

Re: Creating new field with value overlap

sunny_talwar — Wed, 04 May 2016 12:14:47 GMT

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

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 12:16:33 GMT

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?

Re: Creating new field with value overlap

sunny_talwar — Wed, 04 May 2016 12:19:59 GMT

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.

Re: Creating new field with value overlap

jessica_webb — Wed, 04 May 2016 13:00:53 GMT

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!