Grouped Field

[#1]

Hi Community,

I am struggling in creating a grouped field.

For example: I have a status column, where I have

In progress with management

In progress with the team

Provided

Delivered

In review

I need to create a separate column, where I would only have

In progress (which includes: In progress with management, In progress with the team,In review)

Delivered (which includes: Provided and Delivered)

Please advise on the best way to achieve it.

Thank you-

[sunny_talwar]

Try like this:

LOAD status,

          If(Match(status, 'In progress with management', 'In progress with the team', 'In review'), 'In Progress', 'Delivered') as New_Status

FROM Source;

[marcus_sommer]

Maybe something like this:

if(match(Status, 'Provided','Delivered'), 'Delivered', 'In progress') as [Status SubType]

- Marcus

[swuehl]

Maybe like

LOAD Status,

          If(Status LIKE 'In*', 'In progress', 'Delivered') as NewStatus,

     ....

[sunny_talwar · Accepted solution]

Alternatively, you can use ApplyMap or a simple Inline load to do the same thing.

LOAD * INLINE [

status, New_Status

In progress with management, In Progress

In progress with the team, In Progress

Provided, Delivered

Delivered, Delivered

In review, In Progress

];

or

MappingTable:

Mapping

LOAD * INLINE [

status, New_Status

In progress with management, In Progress

In progress with the team, In Progress

Provided, Delivered

Delivered, Delivered

In review, In Progress

];

FactTable:

LOAD status,

          ApplyMap('MappingTable', status) as New_Status

FROM Source;

[#6]

what if i have a third group In Planning

[sunny_talwar]

Extend the if statement to the third group. or use ApplyMap as you can see above

[#8]

It worked! Thank you!

[sunny_talwar]

Awesome