Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
madhubabum
Contributor

Distinct Count Calculation in Script level

Hi Experts,

I am having the following scenario as per my requirement (Move "Distinct Count " calculation to script level)

Dataset :  

TransactionsIDProductIDPname
1100  a
2101  b
3102  c
4103  d
5104  e
2105  f
3106  g

Note : Calculate Distinct Transactions in Script level

In UI Level : Count(Distinct TransactionsID)  = 5,

But Script level calculated field : Count(Dist_TransactionsID) = 7

Here i am attaching the application, excel file..

How we can sort out this issue ,......any help ?

Thanks,

Madhu

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Distinct Count Calculation in Script level

Hi,

Try like this

Temp:

LOAD TransactionsID,

     ProductID,

     Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

DistinctTransactions:

Load DISTINCT

TransactionID,

1 as TransCount

Resident Temp;

Now use Sum(TransCount) in frontend. 

Hope this helps you.

Regards,

jagan.

View solution in original post

18 Replies
Highlighted
Digvijay_Singh
Honored Contributor III

Re: Distinct Count Calculation in Script level

Not sure how you want to write script but below script generates count as 5 -

Temp:

LOAD TransactionsID,

     ProductID,

     Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Distinct:

Load

  Count(DISTINCT TransactionsID) as TransCount

Resident Temp

Highlighted
madhubabum
Contributor

Re: Distinct Count Calculation in Script level

Hi Digvijay Singh,

Thanks for ur reply..

But in my scenario, I want to concatenate or join this field to Temp Table...

How we can acheive.

Highlighted
Chanty4u
Esteemed Contributor III

Re: Distinct Count Calculation in Script level

Temp:

LOAD TransactionsID,

     ProductID,

     Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Join  or Concatenate

Distinct:

Load

  Count(DISTINCT TransactionsID) as TransCount

Resident Temp

Highlighted
balrajahlawat
Esteemed Contributor

Re: Distinct Count Calculation in Script level

may be like this you can try?

Temp:

LOAD TransactionsID,

     ProductID,

     Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Let VCount=count(Distinct TransactionsID);

then use this variable at UI?

Highlighted
madhubabum
Contributor

Re: Distinct Count Calculation in Script level

Hi Chanty,

Thanks for ur reply ....

can u please suggest to use "Join"

Note : I am already used left join, But it won't work

Highlighted
Digvijay_Singh
Honored Contributor III

Re: Distinct Count Calculation in Script level

You can use left Join(Temp) in between but it depends how do you want to see the output - I am not able to understand how you would like to use this count in charts.

Highlighted
Chanty4u
Esteemed Contributor III

Re: Distinct Count Calculation in Script level

what do u want the exact output?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Distinct Count Calculation in Script level

Hi,

Are you couning Distinct TransactionID or TransactionID Count? 

For Distinct:

Count(Distinct TransactionsID)  // Returns 5


Count(TransactionID) // Will give 7


Regards,

Jagan.

Highlighted

Re: Distinct Count Calculation in Script level

Hi,

can you post your script??

Regards

Great dreamer's dreams never fulfilled, they are always transcended.