Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
madhubabum
Creator
Creator

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

18 Replies
madhubabum
Creator
Creator
Author

Hi Jagan Mohan Rao Appala,

Thanks for your reply...

Here I want to calculate Count(Distinct TransactionsID)  in Script level (I want to use this field in UI Level)

Note :

1 ) For counting Distinct Transactions it will take long time (That's why i want to move this calculation to script level)

2) I want to join / Concatenate this field to Main table

Any suggestions ?

Thanks,

Madhu

madhubabum
Creator
Creator
Author

Test:

LOAD TransactionsID,

     ProductID,

     Pname

FROM

(ooxml, embedded labels, table is Sheet1);

left Join(Test)

Load 

     TransactionsID ,

     Count(Distinct TransactionsID) as Dist_TransactionsID Resident Test Group by  TransactionsID;

PrashantSangle

Hi;

Like this

Temp:

LOAD * inline [

TransactionsID, ProductID, Pname

1, 100,  a

2, 101,  b

3, 102,  c

4, 103,  d

5, 104,  e

2, 105,  f

3, 106,  g

];

join

Load Count(Distinct TransactionsID) as count_transaction Resident Temp;

It is giving count 5

Regards

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

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

balrajahlawat
Champion
Champion

try like this?

Test:

LOAD TransactionsID,

     autonumber(TransactionsID) as Count,

     ProductID,

     Pname

FROM

(ooxml, embedded labels, table is Sheet1);

Let VCount=peek('Count',-1,'Test');

Now use $(VCount) at UI.

Hope this will help!!

madhubabum
Creator
Creator
Author

Hi Jagan Mohan Rao,

This logic is working fine.

Thank you very much.....

But i am using Count(TransCount) instead of  Sum(TransCount) ......Now also i am getting the same results.

Note : Can i use Count()    instead  of SUM()

Madhu

balrajahlawat
Champion
Champion

Try and See

it should work!!

dont Use Distinct with Count

puttemans
Specialist
Specialist

Hi there,

Maybe this will suit you?

Temp:

LOAD TransactionsID,

     ProductID,

     Pname

FROM

Test1.xlsx

(ooxml, embedded labels, table is Sheet1);

Map_Distinct:

MAPPING Load

  TransactionsID,

  Count(DISTINCT TransactionsID) as TransCount

Resident Temp

Temp2:

LOAD *,

     Applymap ('Map_Distinct', TransactionsID) as TransCount

RESIDENT Temp;

DROP TABLE Temp;

Regards,

johan

jagan
MVP
MVP

Hi,

Ok.  Both are same, since there is 1 in all the records.

Regards,

Jagan.