CrossTab and Transpose Problems (I think!)

[Bobbins]

Good Day All,

My Valets (demo data) have been playing up again and not been entering the data of tickets correctly into the spreadsheet. Here is the data:

Ticket Number	DateTime	Action
15578	05/09/2022 06:29	Issue Time
15578	11/09/2022 09:15	Issue Time
15578	11/09/2022 17:08	Issue Time
15578	12/09/2022 09:31	Issue Time
15578	13/09/2022 17:43	Issue Time
15578	14/09/2022 06:45	Issue Time
15578	15/09/2022 12:47	Issue Time
15578	05/09/2022 16:05	Paid Time
15578	11/09/2022 16:17	Paid Time
15578	12/09/2022 06:40	Paid Time
15578	12/09/2022 11:40	Paid Time
15578	14/09/2022 02:29	Paid Time
15578	14/09/2022 09:20	Paid Time
15578	15/09/2022 15:11	Paid Time
15579	16/09/2022 16:17	Issue Time
15579	17/09/2022 06:40	Paid Time
15580	18/09/2022 11:40	Issue Time
15581	19/09/2022 02:29	Issue Time
15581	19/09/2022 09:20	Suspended
15581	19/09/2022 02:29	Issue Time
15582	19/09/2022 11:29	Suspended
15582	20/09/2022 09:20	Paid Time

You will notice that:

1) Sometimes the same ticket number is used over and over again but at different dates/times

2) Sometimes the ticket is suspended (not yet paid)

3) Ticket may be issued, suspended and then paid or just issued and paid or issued

What I want to get it to is this:

Ticket Number	Issue Time	Suspended Time	Paid Time
15578	05/09/2022 06:29		05/09/2022 16:05
15578	11/09/2022 09:15		11/09/2022 16:17
15578	11/09/2022 17:08		12/09/2022 06:40
15578	12/09/2022 09:31		12/09/2022 11:40
15578	13/09/2022 17:43		14/09/2022 02:29
15578	14/09/2022 06:45		14/09/2022 09:20
15578	15/09/2022 12:47		15/09/2022 15:11
15579	16/09/2022 16:17		17/09/2022 06:40
15580	18/09/2022 11:40
15581	19/09/2022 02:29	19/09/2022 09:20
15582	19/09/2022 02:29	19/09/2022 11:29	20/09/2022 09:20

I am sure i should be using some CrossTab and Transpose but maybe i am using the wrong tools?

Thank you

[AngelosPachis]

Hi @Bobbins ,

Since each row will always have an issue time you might want to come up with the way to group the sequence the different events happened. What I did is first order the events for each ticket and noticed that each group should restart when every time action is Issue Time as long as the previous two rows don't contain "Issue time" in column [Action]

Hope this helps,

Angelos

[Bobbins]

Hi @AngelosPachis 
Thanks for assistance but your sample workflow does not match your output picture above?

[AngelosPachis]

Hi @Bobbins ,

I downloaded my file again and checked the screenshot I've attached to my post earlier to the workflow's output and they look the same to me. Can you help me understand what are the inconsistencies you've observed? :)

[Bobbins]

If i run the flow as downloaded, this is what i get?

[AngelosPachis]

@Bobbins can you try again please with the attached workflow? I also noticed an inconsistency in the provided input and output (for ticket ID 15582) but should be alright now 

Cheers,

Angelos

[Bobbins]

@AngelosPachis Sadly same problem, now on different lines. If it helps i am using 2020.4

[AngelosPachis]

Interesting, so I asked one of my colleagues to open up the workflow and they see the same as I do so probably something get's mixed up due to the incompatibility of version.

I've tried downgrading my workflow to 2020.4, maybe this will solve the issue? If the issue remains, I'm happy to talk you through each step with screenshots

[AngelosPachis]

@Bobbins one more thing that might be causing an issue (shout out to @DataNath ). When running the workflows, can you make sure that the AMP engine is off? 

You can do that by clicking on your canvas and then from the Configuration window go to Runtime and uncheck the AMP engine box? All workflows should work fine if this is not checked

[Bobbins]

Hi @AngelosPachis 

Turning the AMP engine off still causes the same problem. @DataNath  may be one for you to be aware of.

Looking further, this is my Sort (7) values:

After this, the Crosstab screws it up