topic Re: Counting or concatenating until a value changes in App Development

Counting or concatenating until a value changes

michiel_ — Sat, 16 Nov 2024 06:58:20 GMT

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

Load * Inline[

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

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?

Re: Counting or concatenating until a value changes

sunny_talwar — Thu, 27 Dec 2018 15:18:46 GMT

Start by creating a new field in the script like this

Table:
LOAD * INLINE [
    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:
LOAD *,
	 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)

Re: Counting or concatenating until a value changes

michiel_ — Thu, 27 Dec 2018 16:54:06 GMT

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

Re: Counting or concatenating until a value changes

sunny_talwar — Thu, 27 Dec 2018 17:08:07 GMT

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

Re: Counting or concatenating until a value changes

michiel_ — Wed, 02 Jan 2019 13:57:58 GMT

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!