Qlik Community

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 
Search instead for 
Did you mean: 
RajeevaM061
Contributor II
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)
4 Replies
AshutoshBhumkar
Partner
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:

AshutoshBhumkar_0-1634233817329.png

 

Thanks,

Ashutosh

 

RajeevaM061
Contributor II
Contributor II
Author

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
Specialist
Specialist

Hi @RajeevaM061 ,

Can you please try below

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

 

abhijitnalekar_0-1634239681679.png

 

Regards,
Abhijit
keep Qliking...
RajeevaM061
Contributor II
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)