Distinct Count Calculation in Script level

[madhubabum]

Hi Experts,

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

Dataset :  

TransactionsID	ProductID	Pname
1	100	a
2	101	b
3	102	c
4	103	d
5	104	e
2	105	f
3	106	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

[madhubabum]

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]

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

[jagan · Accepted solution]

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.

[Anonymous]

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]

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

[Anonymous]

Try and See

it should work!!

dont Use Distinct with Count

[puttemans]

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]

Hi,

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

Regards,

Jagan.