Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michiel_
Contributor II
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

 

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

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)
1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

4 Replies
sunny_talwar

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)
michiel_
Contributor II
Contributor II
Author

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

sunny_talwar

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

michiel_
Contributor II
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!