topic Re: Grouped Field in QlikView

Grouped Field

Wed, 25 Nov 2020 16:16:04 GMT

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-

Re: Grouped Field

sunny_talwar — Tue, 24 May 2016 15:05:50 GMT

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;

Re: Grouped Field

marcus_sommer — Tue, 24 May 2016 15:06:21 GMT

Maybe something like this:

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

- Marcus

Re: Grouped Field

swuehl — Tue, 24 May 2016 15:06:46 GMT

Maybe like

LOAD Status,

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

....

Re: Grouped Field

sunny_talwar — Tue, 24 May 2016 15:07:01 GMT

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

];

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;

Re: Grouped Field

Tue, 24 May 2016 16:00:39 GMT

what if i have a third group In Planning

Re: Grouped Field

sunny_talwar — Tue, 24 May 2016 16:03:00 GMT

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

Re: Grouped Field

Tue, 24 May 2016 16:39:30 GMT

It worked! Thank you!

Re: Grouped Field

sunny_talwar — Tue, 24 May 2016 17:09:32 GMT

Awesome