Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
RajeevaM061
Contributor II
Contributor II

Stamp the number based on the lowest order number

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 !

2 Replies
AshutoshBhumkar
Partner
Partner

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:

AshutoshBhumkar_2-1634234457882.png

Thanks,

Ashutosh

abhijitnalekar
Specialist
Specialist

Hi @RajeevaM061 ,

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

 

abhijitnalekar_0-1634239836417.png

 

Regards,
Abhijit
keep Qliking...