Stamp the number based on the lowest order number

[RajeevaM061]

Need your help to create the calculated column based on Count distinct values on the dim “ Package Pattern” w.r.t each invoice number and stamp the smae 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, I have given an answer on the similar question posted by you.

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

[#3]

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

[abhijitnalekar]

Hi @RajeevaM061 ,

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