Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
See attached example
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 |
See attached qvw.