Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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)

View solution in original post

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!