Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Count function in straight table

Hi, I want to count how many of each PO Num exists so I did this simple straight table:

PO NumCount (PO_Num)
123425

I did another straight table and added in the other variables:

DatePO NumStoreLine CostUnit CostCount (PO_Num)
30/09/141234A543.654.365
30/09/141234A543.654.365
30/09/141234A265.854.365
30/09/141234A265.854.365
30/09/141234A1653.254.365

May I know how to get this:

PO NumCount (PO_Num)
12345

DatePO NumStoreLine CostUnit CostCount (PO_Num)
30/09/141234A543.654.361
30/09/141234A543.654.361
30/09/141234A265.854.361
30/09/141234A265.854.361
30/09/141234A1653.254.361

I've already tried formulas such as Count(PO_Num), textcount(PO_Num) and aggr(Count(PO_Num),PO_Num,Store,Date) but I still got 25 as the total count.

Thanks.

5 Replies
MVP
MVP

Re: Count function in straight table

Hi,

Try like this

For Table1 :

=Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))

For Table 2:

Count(DISTINCT PO_Num)

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Count function in straight table

Thanks, but if I use count(distinct the result will only be 1 record for that PO_Num, but it's supposed to be 5 records

MVP
MVP

Re: Count function in straight table

HI,

For this use : =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))

PO NumCount (PO_Num)
12345

For this : Count(DISTINCT PO_Num) - Using distinct you will get 1, I think this is what you are expecting.

DatePO NumStoreLine CostUnit CostCount (PO_Num)
30/09/141234A543.654.361
30/09/141234A543.654.361
30/09/141234A265.854.361
30/09/141234A265.854.361
30/09/141234A1653.254.361

Regards,

Jagan.

Not applicable

Re: Count function in straight table

Thanks, but actually I only want this table:

PO NumCount (PO_Num)
12345

This larger table is only for me to check why the count of PO_Num is 25 instead of 5:

DatePO NumStoreLine CostUnit CostCount (PO_Num)
30/09/141234A543.654.361
30/09/141234A543.654.361
30/09/141234A265.854.361
30/09/141234A265.854.361
30/09/141234A1653.254.361

I still don't get my desired table using ' =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))'.

Is there any chance you might know why each record for PO_Num '1234' is duplicated 5 times? Because now I am getting this table:


PO NumCount (PO_Num)
123425


And to see why there are 25 records for PO_Num '1234', I did this next table:


DatePO NumStoreLine CostUnit CostCount (PO_Num)
30/09/141234A543.654.365
30/09/141234A543.654.365
30/09/141234A265.854.365
30/09/141234A265.854.365
30/09/141234A1653.254.365


Thanks,

MVP
MVP

Re: Count function in straight table

Hi,

You have different line costs that is the PO_Num count is duplicating.

Regards,

Jagan.

Community Browser