Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
BeesKnees
Contributor
Contributor

Count of Data for a specific field

I would like to count a fields that relate to a specific product or product group

How do I number the rows for a specific product and not all the data in the query. 

Labels (1)
1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II

Just add an Order By statement:

Temp:
load *
inline [
Product Number,StartDateTime,EndDateTime,Level,Duration
j8909,10:08:44:26,10:08:50:58,96%,00:00:06:32
j8909,10:08:37:35,10:08:44:26,98%,00:00:06:51
j8909,10:07:26:53,10:08:37:35,96%,00:01:10:42
j8909,09:13:51:24,10:07:26:53,100%,00:17:35:29
j8909,09:13:13:29,09:13:51:24,62%,00:00:37:55
j8909,09:07:36:07,09:13:13:29,64%,00:05:37:22
j8910,09:07:28:23,09:07:36:07,64%,00:00:07:44
j8911,09:07:23:14,09:07:28:23,64%,00:00:05:09
j8912,09:04:19:51,09:07:23:14,64%,00:03:03:23
j8913,08:21:31:53,09:04:19:51,75%,00:06:47:58
j8914,08:21:04:40,08:21:31:53,71%,00:00:27:13];


Final:
Load *,
if([Product Number]=Peek([Product Number]),peek(Sequential)+1,1) as Sequential
Resident Temp
order by StartDateTime;
Drop Table Temp;
Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

6 Replies
micheledenardi
Specialist II
Specialist II

Your question is not so clear... can you explain better? Thx

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
BeesKnees
Contributor
Contributor
Author

I would like script that gives me a sequential number for each product.
Thus for product j8909 I would like the number for the earliest date stamp
record to be one then 2 for the second one, then 3 for the third
Autonumber does not work because the fields in the database wont sort how i
want them to.
Product Number StartDateTime EndDateTime Level Duration
j8909 10:08:44:26 10:08:50:58 96% 00:00:06:32
j8909 10:08:37:35 10:08:44:26 98% 00:00:06:51
j8909 10:07:26:53 10:08:37:35 96% 00:01:10:42
j8909 09:13:51:24 10:07:26:53 100% 00:17:35:29
j8909 09:13:13:29 09:13:51:24 62% 00:00:37:55
j8909 09:07:36:07 09:13:13:29 64% 00:05:37:22
j8910 09:07:28:23 09:07:36:07 64% 00:00:07:44
j8911 09:07:23:14 09:07:28:23 64% 00:00:05:09
j8912 09:04:19:51 09:07:23:14 64% 00:03:03:23
j8913 08:21:31:53 09:04:19:51 75% 00:06:47:58
j8914 08:21:04:40 08:21:31:53 71% 00:00:27:13
micheledenardi
Specialist II
Specialist II

Maybe something like this:

Temp:
load *,
if([Product Number]=Peek([Product Number]),peek(Sequential)+1,1) as Sequential
inline [
Product Number,StartDateTime,EndDateTime,Level,Duration
j8909,10:08:44:26,10:08:50:58,96%,00:00:06:32
j8909,10:08:37:35,10:08:44:26,98%,00:00:06:51
j8909,10:07:26:53,10:08:37:35,96%,00:01:10:42
j8909,09:13:51:24,10:07:26:53,100%,00:17:35:29
j8909,09:13:13:29,09:13:51:24,62%,00:00:37:55
j8909,09:07:36:07,09:13:13:29,64%,00:05:37:22
j8910,09:07:28:23,09:07:36:07,64%,00:00:07:44
j8911,09:07:23:14,09:07:28:23,64%,00:00:05:09
j8912,09:04:19:51,09:07:23:14,64%,00:03:03:23
j8913,08:21:31:53,09:04:19:51,75%,00:06:47:58
j8914,08:21:04:40,08:21:31:53,71%,00:00:27:13];

2022-02-10 11_34_11-Window.png 

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
BeesKnees
Contributor
Contributor
Author

This works, but I have to start the sequence at the earliest time stamp to
the latest.

The data that I provided is just a sample.


micheledenardi
Specialist II
Specialist II

Just add an Order By statement:

Temp:
load *
inline [
Product Number,StartDateTime,EndDateTime,Level,Duration
j8909,10:08:44:26,10:08:50:58,96%,00:00:06:32
j8909,10:08:37:35,10:08:44:26,98%,00:00:06:51
j8909,10:07:26:53,10:08:37:35,96%,00:01:10:42
j8909,09:13:51:24,10:07:26:53,100%,00:17:35:29
j8909,09:13:13:29,09:13:51:24,62%,00:00:37:55
j8909,09:07:36:07,09:13:13:29,64%,00:05:37:22
j8910,09:07:28:23,09:07:36:07,64%,00:00:07:44
j8911,09:07:23:14,09:07:28:23,64%,00:00:05:09
j8912,09:04:19:51,09:07:23:14,64%,00:03:03:23
j8913,08:21:31:53,09:04:19:51,75%,00:06:47:58
j8914,08:21:04:40,08:21:31:53,71%,00:00:27:13];


Final:
Load *,
if([Product Number]=Peek([Product Number]),peek(Sequential)+1,1) as Sequential
Resident Temp
order by StartDateTime;
Drop Table Temp;
Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
BeesKnees
Contributor
Contributor
Author

The data is refusing to sort.

I am not sure what the problem is.