wildmatch and sum

[raadwiptec]

Hi ,

I have a pivot table..

first I need to consolidate all the wild match name to one name

for ex: column name is 'Postion' - data is as below.

moved, transit, moved out,moved in,outin moved, transitout,transitin..etc

so I need to have all the moved in wildmatch and have common name as 'moved'

secondly I have cost for each position for

moved

cost is 100 pounds

moved out

cosit is 150 pounds

etc.

so when making the postion as moved ..I also need the sum of all the cost ..it should be like this

Moved(It includes as moved)

250 (It is sum of cost for all moved)

[PradeepReddy]

We can achieve this in 2 ways

1) In Script:

Load

Country,

Position,

Cost,   

IF(WildMatch(Position,'*moved*'),'moved', if(WildMatch(Position,'*transit*'),'transit')) as poition_New

From Source;

2) In Front-End

Dimension: Country

                  IF(WildMatch(Position,'*moved*'),'moved', if(WildMatch(Position,'*transit*'),'transit'))

Expression : Sum(Cost)

Thanks,

Pradeep

[jonas_rezende · Accepted solution]

Hi, raadwiptec g .

Try:

In Script

Table:

LOAD

if(WILDMATCH(POSITION,'*Moved*'),'Moved','Transit') AS Category,

POSITION,

COST;

LOAD * Inline [

POSITION, COST

Moved, 100

Moved Out, 150

MovedIn, 200

Out Moved, 250

In Transit, 100

Out Transit, 200

];

In Sheet.

Hope this helps.

[raadwiptec]

from the script wise it is good from sunny and jonas,I need to check more whether all the data is summing up.

for the non wildmatched I want to move to another column can I attain in the same,

for ex

I hae another couple of code called ei,e2,s3,s5,  ..it is a big list with more that 60- 70 codes. it is difficult to each code to show.so if its other than moved and transit I want move to non-category

[jonas_rezende]

How would be the result in pivot table? Examplifies, please.

[raadwiptec]

I think I attained it.. by adding in same expression itself. still you can let me know your inputs.it would be useful

                          Category !  Non Category¦    Cost

     UK                    Moved                                 200

     USA                 Transit                                  300

     RW                                           e2                100

[tyagishaila]

Table1:

LOAD

     POSITION,

     COST;

LOAD * Inline [

POSITION, COST

Moved, 100

MovedOut, 150

MovedIn, 200

OutMoved, 250

InTransit, 100

OutTransit, 200

];

Concatenate

Table2:

Load 

          'Total-Moved' as POSITION,

          COST

Resident Table1

where wildmatch(POSITION, '*Moved*);

POSITION field Sort by Load Order 'Original'

By this Total-Moved will show only sum(cost) of Moved* only

[Anonymous]

if(wildmatch('postion =moved') sum({<position='moved'>}cost)

[Anonymous]

if(wildmatch(columnname 'value') sum({<field name='vaule'>}cost)

[Anonymous]

=if(WildMatch(POSITION,'Moved'), sum({<POSITION={'Moved'}>}COST))

try this expression

[jonas_rezende]

What is the relationship between Non Category and Category?