Hello Everyone!
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.
Here is what I have so far:
CountOfKey:
LOAD *,
PurchaseOrder as Key
Resident PooledPOComplianceRawData;
DROP Table PooledPOComplianceRawData;
CountOfKeyCheck:
LOAD *,
IF(PEEK(Key)=Key,PEEK(Check)+1, 0) as Check
Resident CountOfKey
Order By Key;
DROP Table CountOfKey;
Is the above script really doing what I need it to do as shown in the table below?
Example:
| Unique Key (Purchase Order) | Count of Key (occurrences) | Date Compliant = 1 or 0 | Sum of 1 or 0 for Compliance | if(count = sum of compliance), Entire PO is compliant |
| 123345679 | 9 | 1 | 7 | FALSE |
| 123345679 | | 0 | | |
| 123345679 | | 1 | | |
| 123345679 | | 0 | | |
| 123345679 | | 1 | | |
| 123345679 | | 1 | | |
| 123345679 | | 1 | | |
| 123345679 | | 1 | | |
| 123345679 | | 1 | | |
