Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Counting or concatenating until a value changes

Hi,

I a production environment I'm trying to analyse whether a batch of products have been produced in a logical order.

It occurs that I have something like

Batch,Time,Product

1,11:01,A

1,11:02,A

1,11:03,A

1,11:04,B

1,11:05,B

1,11:06,A

1,11:06,B

2,12:30,A

...

];

Counting/Concat Distinct is obvious, but this 'ignores' the sequentiality of the data.

In a diagram I want to make a check for these non-conformities. The result should either be

Batch   Number of sequencial products

1            4

2            1

Or

Batch  Sequence of products

1           A,B,A,B

2           A

I've found this other topic but here it's put into the script. I'm looking for something similar.

Anyone an idea?

Labels (3)

• ### function

1 Solution

Accepted Solutions
MVP

Start by creating a new field in the script like this

```Table:
Batch, Time, Product
1, 11:01, A
1, 11:02, A
1, 11:03, A
1, 11:04, B
1, 11:05, B
1, 11:06, A
1, 11:06, B
2, 12:30, A
];

FinalTable:
If(Batch = Previous(Batch), If(Product = Previous(Product), Peek('Num'), RangeSum(Peek('Num'), 1)), 1) as Num
Resident Table
Order By Batch, Time;

DROP Table Table;```

and then you can create your count and concat like this

Count

`=Count(DISTINCT Num)`

Concat

`=Concat(DISTINCT Product, ',', Num)`
4 Replies
MVP

Start by creating a new field in the script like this

```Table:
Batch, Time, Product
1, 11:01, A
1, 11:02, A
1, 11:03, A
1, 11:04, B
1, 11:05, B
1, 11:06, A
1, 11:06, B
2, 12:30, A
];

FinalTable:
If(Batch = Previous(Batch), If(Product = Previous(Product), Peek('Num'), RangeSum(Peek('Num'), 1)), 1) as Num
Resident Table
Order By Batch, Time;

DROP Table Table;```

and then you can create your count and concat like this

Count

`=Count(DISTINCT Num)`

Concat

`=Concat(DISTINCT Product, ',', Num)`
Contributor II
Author

Thanks, this looks good, but no possibility to do this on the worksheet only then?

MVP

Might be possible, but will require a complex expression... you don't have access to the script?

Contributor II
Author

I do have access, but was wondering if it would be possible on the worksheet. Your example before will work, I'll start working on the script:)

Thanks!