Is there a way to count the number of times each unique key (Purchase Order) appears in the raw data and have the sum of the count of occurrences entered into a new column? So if the Purchase Order only occurs 1 time, 1 will be entered into the new column. If the Purchase Order appears 9 times, 9 is entered into the new column. I have over 1 million Purchase orders I would like to track compliance for. I need to look at compliance at the line item level first to determine if the entire PO is compliant or non-compliant.
Criteria for PO Compliance:
PO Date <= Invoice Date, PO Compliant
PO Date > Invoice Date, PO Non-Compliant
Any PO line item date > Invoice Date, ENTIRE PO Non-Compliant
I would like to execute this in the script on the backend since it will apply throughout the entire app and not just one sheet. I checked in Qlik Community but I could not find much that matched with what I am trying to do. I trying playing with posts that were similar but I just ended up confusing myself.
So in the example below, I need help calculating the “Count of Key” field for each Purchase Order and somehow achieving that 9 in the Count of Key column. I got the "Date Compliance" calculation to work. I just keep getting stuck on the "Count of Key", "Sum for Compliance", and "Count = Sum ..." columns.