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 NumberDateTimeAction1557805/09/2022 06:29Issue Time1557811/09/2022 09:15Issue Time1557811/09/2022 17:08Issue Time1557812/09/2022 09:31Issue Time1557813/09/2022 17:43Issue Time1557814/09/2022 06:45Issue Time1557815/09/2022 12:47Issue Time1557805/09/2022 16:05Paid Time1557811/09/2022 16:17Paid Time1557812/09/2022 06:40Paid Time1557812/09/2022 11:40Paid Time1557814/09/2022 02:29Paid Time1557814/09/2022 09:20Paid Time1557815/09/2022 15:11Paid Time1557916/09/2022 16:17Issue Time1557917/09/2022 06:40Paid Time1558018/09/2022 11:40Issue Time1558119/09/2022 02:29Issue Time1558119/09/2022 09:20Suspended1558119/09/2022 02:29Issue Time1558219/09/2022 11:29Suspended1558220/09/2022 09:20Paid 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 NumberIssue TimeSuspended TimePaid Time1557805/09/2022 06:29 05/09/2022 16:051557811/09/2022 09:15 11/09/2022 16:171557811/09/2022 17:08 12/09/2022 06:401557812/09/2022 09:31 12/09/2022 11:401557813/09/2022 17:43 14/09/2022 02:291557814/09/2022 06:45 14/09/2022 09:201557815/09/2022 12:47 15/09/2022 15:111557916/09/2022 16:17 17/09/2022 06:401558018/09/2022 11:40 1558119/09/2022 02:2919/09/2022 09:20 1558219/09/2022 02:2919/09/2022 11:2920/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

CrossTab and Transpose Problems (I think!)

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

Developer

Accepted answers

All comments

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

sample_workflow.yxmd

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

sample_workflow (1).yxmd

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

sample_workflow_2020.4.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703