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

[RajeevaM061]

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 !

[AshutoshBhumkar]

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

[RajeevaM061]

Thanks you for the reply.

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.

[abhijitnalekar]

Hi @RajeevaM061 ,

Can you please try below

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

[RajeevaM061]

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)