How to combine or summarise a sub-set of data for a table

[burgersurfer]

Hi

I have a set of data that looks like this

Code Description

1000 Training

1001 Tennis

1002 Running

...

1011 Other

28 Fitness

29 Skate

30 Socks

The code is refenced in a sales table, that I want to analyse. But since some of the data is "junk", I want to sum it into one code. So I want to take the sales associated with codes 28, 29 and 30 above, and add that to the 1011 (Other) code when creating the table.

Above is only an sample of the data set.

Any ideas?

[#2]

Hi,

Just modify your script when you read the lookup table

Load *

Code ,

if( Code = 28 or Code=29 or Code = 30 , 'Other' , Description ) as Description

from ....

Then in your chart use Description as dimension

regards

jj

[burgersurfer]

Thanks Jean-Jacques

I am loading this as an inline table, and get an error when running the code

Format now is:

Load * Inline [

CategoryCD, Category

1, 'Football'

2, 'basketball'

..

1001, 'Running'

...

],

If( CategoryCD = 1 or CategorCD =2 or ..., '1011', CategoryCD) as CategoryCD

?

[#4]

Just change comma after ] with ;

jj

[burgersurfer]

Sorry JJ, do not get a result here, when I change to a semicolon, QV looks for a ) bracket

OR statements in the "If" also do not change to blue text when using a ;

[#6]

Oups !

try to put the if before

Load
If( CategoryCD = 1' or CategoryCD ='2' , '1011', CategoryCD) as CategoryCD , Category
Inline [
CategoryCD, Category
1, 'Football'
2, 'basketball'
1001, 'Running'
1011, 'Other'] ;

jj

[burgersurfer]

Thanks JJ, that clears the error messages

However, I now have numerous fields showing "1011" in the table, where I only wanted one?

[#8]

Hi

Please can you show me the chart you want to display ? it will be easier to help you.

regards

[burgersurfer]

Hi jj, sorry for the delay.

I want to use this information in a pivottable, and will be using it in some other calculations later on as well.

Herewith a screenshot showing the category table after the If statement, and an example of the table I want to create. So I do not want "1011", or the description that goes with it multiple times across the top of the pivot, I want to reduce the categories to only 11 - from code 1001 to code 1011.

Hope this makes sense.

BTW, I also see now that some codes became duplicated, e.g. Basketball is now 1002 and 1011.

[#10]

Maybe Jet Lag Burg ! i'm in Paris

I see clear now.

What you have to do is to populate the category you want with 'Other', don't change CategoryCD

So let's try if you want to group CategoryCD= 1 or 2 (maybe it's not your needs, but adapt the condition)

Load
If( CategoryCD = 1 or CategoryCD =2 , 'Other', Category) as Category , CategoryCD
Inline [
CategoryCD, Category
1, 'Football'
2, 'basketball'
1001, 'Running'
1011, 'Other'];

Then your Pivot Table is OK

2 dimensions : Store & category

1 expression : sum( Value) BTW what is the R just before the figures ?

Hope it will be ok now

regards

JJ