Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count of Duplicated Unique Values in QlikView

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

 

0 Replies