How to create sub groups?

[peschu123]

Hi,

i have the following table:

Order	Sub Order	Start	Ende	Description
123456	0200	09.04.2013	09.04.2013	Freigabe 1
123456	0250	08.05.2013	08.05.2013	Lagerbezug
123456	0301	08.05.2013	08.05.2013	something
123456	0400	13.05.2013	13.05.2013	something
123456	1950	28.06.2013	28.06.2013	Freigabe 2
123456	1951	02.07.2013	02.07.2013	something
123456	2000	04.07.2013	04.07.2013	something
123456	2100	28.06.2013	28.06.2013	something
123456	7400	18.07.2013	22.07.2013	something
123456	9850	22.07.2013	22.07.2013	Kontrolle
123456	9851	26.07.2013	26.07.2013	Report

And at the end I want it to look like this:

Order	Sub Order	Start	Ende	Description	Status
123456	0200	09.04.2013	09.04.2013	Freigabe 1	1
123456	0250	08.05.2013	08.05.2013	Lagerbezug	2
123456	0301	08.05.2013	08.05.2013	something	2
123456	0400	13.05.2013	13.05.2013	something	2
123456	1950	28.06.2013	28.06.2013	Freigabe 2	3
123456	1951	02.07.2013	02.07.2013	something	3
123456	2000	04.07.2013	04.07.2013	something	3
123456	2100	28.06.2013	28.06.2013	something	3
123456	7400	18.07.2013	22.07.2013	something	3
123456	9850	22.07.2013	22.07.2013	Kontrolle	4
123456	9851	26.07.2013	26.07.2013	Report	5

Text in bold shows the point where the status changes.

I would like to precalculate this field in my script.

My problem is that I can just use the "Description" field to determine the status, all other fields change from one order to another. And further I need to flag the fields in between too.

At the moment I use Set Expression like this in my table to calculate the Days for the specific status:

=only({<Description={'Lagerbezug'}>}[Start]) - only({<Description={'Freigabe 1'}>}[Start]) & 'Days'

And because this would be too easy, sometimes a relevant description is missing.

For example in this case the description "Freigabe 2" could be missing. I think the best workaround would be to use Status 2 until it changes to "Kontrolle" (Status 4).

I appreciate any help at this topic.

Thank you in advance, Peter.

[Gysbert_Wassenaar · Accepted solution]

See attached example

[peschu123]

Wow Gysbert,

as I can see it's exactly what I need. I assume with wildmatch it works the same way?

Thank you very much.

EDIT:

I realized one little point. The selection must be like this. I tried to edit the formula, but I just don't get it to be sorted like this:

123456	200	09.04.2013	09.04.2013	Freigabe 1	1
123456	250	08.05.2013	08.05.2013	Lagerbezug	2
123456	301	08.05.2013	08.05.2013	something	3
123456	400	13.05.2013	13.05.2013	something	3
123456	1950	28.06.2013	28.06.2013	Freigabe 2	3
123456	1951	02.07.2013	02.07.2013	something	4
123456	2000	04.07.2013	04.07.2013	something	4
123456	2100	28.06.2013	28.06.2013	something	4
123456	7400	18.07.2013	22.07.2013	something	4
123456	9850	22.07.2013	22.07.2013	Kontrolle	4
123456	9851	26.07.2013	26.07.2013	Report	5

[Gysbert_Wassenaar]

See attached qvw.