Count rows after a flag

[LesJean]

Hello everyone,

I'm trying to count the numbers of rows after a flag (including the flag itself) and I was wondering how I should proceed, here's a data example:

Date | Name | Flag
2019-04-04| 111 | X
2019-04-05| AAA | 
2019-04-06 | BBB | X
2019-04-07 | CCC | 
2019-04-08 | DDD |
2019-04-09 | EEE | 
2019-04-10 | FFF | 

So in this scenario, the value returned from the count should be 5 (the last flagged row + the 4 following rows that aren't flagged)

To give you another example, in this case:

Date | Name | Flag
2019-04-04| 111 | X
2019-04-05| AAA | 
2019-04-06 | BBB | X
2019-04-07 | CCC | 
2019-04-08 | DDD |
2019-04-09 | EEE | 
2019-04-10 | FFF | 
2019-04-11 | JJJ | X

The count should return 1.

Thank you for your assistance,

LesJean

[marwen_garwachi]

Hi, 

I understand yur need but to give you a response i need more informations : 

What is the flag ? Is it constant (inchanged) ?  What is the relation between the flag and the other fields ?  Where do you want to put the formula  (in a graph, on load, KPI, Table etc...) ? 

To give you a simple answer : it's just not possible but you can find a work around to achieve your goal 😉 

Marwen 

[dwforest]

In load script:

LOAD
Date,
Name,
Flag,
If(Flag='X',Previous(Flag)+1) as Flag
FROM yourdatasource
;

[jpenuliar]

Just thinking about it,
count of Rows (starting from Flag X) = (Max Row - index of value x) +1
so in your first example:
count of Rows (starting from Flag X) = (7 - 3) +1 = 5 rows

[LesJean]

Hello Marwen,

Yeah this is a pretty complex situation, I'll try to explain it as best as I can.

The application is refreshed everyday, and everyday, a new day is added (the new data from today). Here's a example of data close to what's going on in the application:

Product | DateAdded | NextAvailabilityDate | Flag
01 | 2019-04-01 | 2019-06-10 | X
02 | 2019-04-01 | 2019-04-03 | X
01 | 2019-04-02 | 2019-06-10 |
02 | 2019-04-02 | 2019-04-03 |
01 | 2019-04-03 | 2019-06-10 |
01 | 2019-04-04 | 2019-06-10 |

Think of this as a product, that, when it's goes in a state of back order, is added to this table with the timestamp from today and the date it should be available in the future. In the example above, we can see that the product 02 was made re-available on the 04-03 after a back order that started on the 04-01, which is why there are no new entries for the 02 product after the 04-02.

The flag symbolizes the start of a new back order sequence.

Now, if I were to run the script on the 04-04. Product 01 should count 4 days in back order and 0 changes on the availability date. Product 02 should count 0 days in back order and 0 changes as it isn't currently in back order.

Now, let's say, on the next day, the table looks like this:

Product | DateAdded | NextAvailabilityDate | Flag
01 | 2019-04-01 | 2019-06-10 | X
02 | 2019-04-01 | 2019-04-03 | X
01 | 2019-04-02 | 2019-06-10 |
02 | 2019-04-02 | 2019-04-03 |
01 | 2019-04-03 | 2019-06-10 |
01 | 2019-04-04 | 2019-06-10 |
01 | 2019-04-05 | 2019-06-15 |
02 | 2019-04-05 | 2019-05-05 | X

Two things happened, in the first place, the product 02 is in a new back order, which is flagged. The product 01 availability date changed. In this scenario, I would like to get, for product 01, 5 days in back order and 1 change of the availability date. For product 02, 1 day in back order and no changes of the availability date since it's the first date in this new sequence of back order.

The results of these calculations (the numbers of days in back order and the number of changes of the availability date in this sequence) would be stored in another table like so:

Product | DateBeginningBackOrder | NumbersOfAvailabilityDateChanges
01 | 2019-04-01 | 1
02 | 2019-04-01 | 0
02 | 2019-04-05 | 0

I hope this is clear enough to give you an idea of what I'm required to do.

Thank you for reading,

LesJean