How to calculate number of active count from the logs

[Adarsh]

Hi..

I have the logs which contain DATE, NUMBERS AND PACKAGES ACTIVATED ON THEM...

HOW CAN I GET THE COUNT OF ACTIVE PACKAGES AND NUMBERS ON ANY PARTICULAR DATE FROM THIS.

EG.

Date                             Command                     VC                                     Pack Name
01-01-2020             Activate                     1234567890                     Pack 2
02-01-2020             Activate                     1234567890                     Pack 1
20-01-2020             Deactivate               1122334455                     Star Sports 1
20-01-2020             Deactivate               1122334455                     Pack 1
20-01-2020             Deactivate               1122334455                     Pack 3
20-01-2020             Deactivate               1122334455                     Pack 2
27-01-2020             Activate                     1122334455                     Pack 2
27-01-2020             Activate                     1122334455                     Pack 3
27-01-2020             Activate                     1122334455                     Star Sports 1
27-01-2020             Activate                     1122334455                     Pack 1
28-01-2020             Deactivate               1122334455                     Star Sports 1
30-01-2020             Deactivate               1234567890                     Pack 2
30-01-2020             Deactivate               1234567890                     Pack 1
01-02-2020             Activate                     1234567890                     Pack 2
01-02-2020             Activate                     1234567890                     Pack 1

Thanks

[danilang · Accepted solution]

Hi @Adarsh 

You can do it like this.

After cleaning your data, get the min and max dates and generate a list of all possible dates.  Perform a X join on the list of pack names to get a grid wiht all possible combinations.  Join this to your input on Date and Pack and union the unmatched rows from the grid.  Sort and use a Multi Row formula to get the active status for each pack on each date.  summarize to get the number and statuses of each pack on each date and keep only the active ones.  Join this to the list of dates and add 0 count for the ones that don't match.  At the end of it all you end up with

Dan

[Adarsh]

Hello Sir,

Need your help to modify the workflow you suggested.

In the workflow if there is any depairing command, then the pack name is appearing as active instead of deactive after receiving of depairing command. Can you suggest the modification needed to correct the same.

Attaching the workflow for your reference.

Thanks for the help.

[danilang]

Hi @Adarsh 

Ah, the joys of getting new requirements!  What is the purpose of the depair command?  I notice that there is no pack name associated with it.  How do you know which pack the depair command relates to?

Dan

[Adarsh]

Hi Danilang

The depair command relates to deactivation only... the box has been depaired from the VC i.e., the VC has been deactivated and all the packs running on that VC has been deactivated...

All packs must come as deactivate after the Depair command.

[danilang]

@Adarsh 

So to be clear, all packs that are active on that VC and only those that are active on that VC when the Depair command is received are set to Not Active.  Packs that are not active on that VC are not affected.  Are these 2 statements correct?

Is there an equivalent Pair command?   Or any other command that may affect the logic. 

Dan

[Adarsh]

@danilang

Yes the statement is correct.

All the packs that are active on that VC when the Depair command is received are set to Not Active.  Packs that are not active on that VC are not affected .

There is no equivalent Pair command, only activate command.

[danilang]

Hi @Adarsh 

I just need a clarification

From the image above, we see that Pack was assigned to VC 122334455 on the Jan 15.  It was then deactivated on VC 1010101010 on the 27th, activated and then depaired from VC 1010101010 on the 28th.  At this point Pack 6 is still active on VC 122334455.  is this correct?  And Pack 6 remains depaired with VC 1010101010 until it is activated again

Dan

[Adarsh]

@danilang

 VC 122334455 and VC 1010101010 both are different and they are not linked.

Pack 6 on VC 122334455 would continue as activate until it does not receive a deactivate command.

In case of VC 1010101010 , as it has received a depair command , Pack 6 running on VC 1010101010 should come as deactivated from 28th Jan onwards until it is activated again.

Same will be case of VC 9876543210. Depair command received on 17-01-2021. Before this date i.e., 12th Jan to 16th Jan Pack 4 and Pack 8 will come as Activate. From 17th Jan onwards it will come as deactivated until it is activated again.

It is just that if any VC receives a depair command, all the packs Active on that VC will be deactive from that day onwards.

Hope I am able to clarify your doubts.

[danilang · Accepted solution]

Hi @Adarsh 

Here's an updated workflow to account for the Depair commands.  The main difference is the extra join in the middle to ensure that Depair command affect all packs on the VC and then an extra Sort/Multi Row combo near the end to build a running list of active packs on each VC

It's somewhat more complicated than the original, but you should be able to take it from here. 

Note: that I added an extra Activate command into the input (the last row) to ensure the logic would properly handle an activate that came after a Depair command.

Dan