Group records in the load script

[bgreennc]

Hello Qlik experts,

I have ran into a roadblock with my application development and I am hoping someone can help me.

I work in a manufacturing environment. We have RFID tags attached to tuggars that pull carts to an assembly line. The carts also have RFID tags attached to them.

Goal:

Count how many carts the tuggar is pulling when it passes thru a door.

Scenario:

There can be up to 4 different tuggars (Labeled 1111, 2222, 3333, or 4444)

There are three doors into our warehouse:

Door 1 - Exit only

Door 2 - Exit and entry

Door 3 - Entry only

The tuggar can go in or out of the warehouse without pulling any kits.

I have attached test data also.

Based on the different scenarios, I am not sure how to accomplish this in the load script. Any help you can provide would be much appreciated.

[Kushal_Chawda]

@bgreennc  what is the expected output? 

[bgreennc]

Thank you for your response.

Great question! I would like to see charts that will show:

1) How many kits the tuggar is pulling each time they go in or out a door

2) The average kits the tuggar is pulling each time they go in or out a door

3) How many times a tuggar goes in our out a door with no kits.

Please let me know if you need more information, or if you have any questions.

Thanks

[Kushal_Chawda]

@bgreennc  How do we find the number of Kits associated with specific tuggar?

[bgreennc]

In the screen shot below driver 3333 goes into the door 4861MSCDoor3In pulling two kits based on the time stamp:

In the next screen shot, the next time tuggar 3333 is seen is when he goes thru the door 4861MSCDoor3In pulling two kits based on the time stamp. What is tricky in this screen shot is it looks like there are 3 kits, but kit 917EK1 is not going in the same door, so it would not be counted as being pulled by tuggar 3333. After going in door 3, tuggar 3333 can be seen going out door 1 at 12:22:25

Please let me know if you have any more questions.

Thanks.

[Kushal_Chawda]

@bgreennc  try below

Data:
LOAD
    Door,
   Timestamp(Timestamp#("Time Stamp",'YYYY-MM-DDThh:mm:ss')) as "Time Stamp",
   if(match("Tuggar / Kit number",1111,2222,3333,4444),1,0) as tuggar_flag,
    "Tuggar / Kit number"
FROM [lib://Data/Files/Test data.xlsx]
(ooxml, embedded labels, table is Sheet2);

New:
Load *,
     if(Door=Previous(Door) and tuggar_flag=0,Peek('Time Stamp Tuggar'),"Time Stamp") as "Time Stamp Tuggar",
     if(Door=Previous(Door) and tuggar_flag=0,Peek('Tuggar'),"Tuggar / Kit number")  as Tuggar
Resident Data
Order by Door,"Time Stamp";

Drop Table Data;

Left Join(New)
Load Door,
     "Time Stamp Tuggar" as "Time Stamp",
     Tuggar as "Tuggar / Kit number",
     Count("Tuggar / Kit number") as count_of_kits
Resident New
Where tuggar_flag=0
Group by Door,
     "Time Stamp Tuggar",
     Tuggar;
     
Left Join(New)
Load Distinct Door,
     "Time Stamp Tuggar" ,
     "Tuggar / Kit number",
     1 as flag_tuggar_without_kits
Resident New
Where "Tuggar / Kit number"=Tuggar and isnull(count_of_kits);