# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor II

## Count distinct values on the dim and stamp the number based on the lowest order number

Need your help to Count distinct values on the dim “ Package Pattern” w.r.t each invoice number and stamp the number based on the lowest order number in the sheet.

For Example :  For the Invoice “ 1111”, I have 1 distinct package pattern and need to stamp the count on the lowest order number in the table  . In the below  example, it’s 3rd row since 5 is lowest order number for the specific invoice.

 Sales Invoice No Order Number Package Pattern 1111 10 Part A, Part B, Part C 1111 25 Part A, Part B, Part C 1111 5 Part A, Part B, Part C 1111 30 Part A, Part B, Part C

Expected result :

 Sales Invoice No Order Number Package Pattern Sequence Qty 1111 10 Part A, Part B, Part C 0 1111 25 Part A, Part B, Part C 0 1111 5 Part A, Part B, Part C 1 1111 30 Part A, Part B, Part C 0

Thank you !

Labels (2)

• ### Qliksense Function

4 Replies
Partner

Hello,

You can use below script to get the 1 as Sequence Quantity grouped by Sales Invoice.

Table:
Load [Sales Invoice No]&'-'&[Order Number] as Key, [Sales Invoice No],[Order Number],[Package Pattern]

Inline [
Sales Invoice No,Order Number,Package Pattern

1111,10,'Part A, Part B, Part C'
1111,25,'Part A, Part B, Part C'
1111,5,'Part A, Part B, Part C'
1111,30,'Part A, Part B, Part C'
];

MinOrderNo:
Load [Sales Invoice No]&'-'&Min([Order Number]) as Key,
'1' as [Sequence Qty]
Resident Table Group by [Sales Invoice No];

Output:

Thanks,

Ashutosh

Contributor II
Author

I can't create in the load script, since all the data fields are part of
different dimensions and will be more complicated.

I am looking for the Set analysis /functions,so that I can implement them
in the sheet.
Specialist

Hi @RajeevaM061 ,

if([Order Number]=aggr(min( [Order Number]),[Sales Invoice No]),1,0)

Regards,
Abhijit
keep Qliking...
Contributor II
Author

Thank you for the response.

I have used the below one to get the expected value.

Coalesce(Aggr(Count({<[Order Number]={\$(=Min([Order Number]))}>}1),[Sales Invoice No],[Order Number]),0)

Tags
Community Browser