Count of Duplicated Unique Values in QlikView

Wed, 25 Nov 2020 16:16:04 GMT

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

topic Count of Duplicated Unique Values in QlikView in QlikView

Count of Duplicated Unique Values in QlikView