Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have this table in excel (the duplicated records are supposed to be):
PO_num | Date | Store | Dr | Unit Price | Value |
---|---|---|---|---|---|
345 | 30/09/14 | A | 7645 | 234 | 7645 |
345 | 30/09/14 | A | 7645 | 234 | 7645 |
345 | 30/09/14 | A | 2345 | 234 | 2345 |
345 | 30/09/14 | A | 2345 | 234 | 2345 |
345 | 30/09/14 | A | 3456 | 234 | 3456 |
But when I created this table box in Qlikview it combines the duplicated records into 1 record:
PO_num | Date | Store | Dr | Unit _Price | Value |
---|---|---|---|---|---|
345 | 30/09/14 | A | 7645 | 234 | 7645 |
345 | 30/09/14 | A | 2345 | 234 | 2345 |
345 | 30/09/14 | A | 3456 | 234 | 3456 |
My goal is to count the number of records for PO_num '345'. I'm currently getting this:
PO_num | Count (PO_num) |
---|---|
345 | 3 |
I applied the formulas 'count(PO_num)', 'count(aggr(PO_num,Date,Store,Dr,Unit_Price,Value))', 'count(Aggr(Distinct PO_num,Date,Store,Dr,Unit_Price,Value))' and 'count(Aggr(count(Distinct PO_num),Date,Store,Dr,Unit_Price,Value)) in a straight table but it counts only 3 records of PO_num '345' when it's supposed to be 5 records. How can I achieve this straight table? :
PO_num | Count (PO_num) |
---|---|
345 | 5 |
Thanks.
In the table that you do the calculation you can add another column that makes the count of each line distinct.
You can use the RecNo for example in order to make every line distinct and then hide this column inside the chart.This way the count(PO_num) will count all 5 columns
Hi,
look at the concept of rowno and recno.
http://community.qlik.com/blogs/qlikviewdesignblog/2014/08/29/recno-or-rowno
Regards
ASHFAQ