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

[Digvijay_Singh]

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

[madhubabum]

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.

[Chanty4u]

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

[Anonymous]

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?

[madhubabum]

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

[Digvijay_Singh]

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.

[Chanty4u]

what do u want the exact output?

[jagan]

Hi,

Are you couning Distinct TransactionID or TransactionID Count? 

For Distinct:

Count(Distinct TransactionsID)  // Returns 5

Count(TransactionID) // Will give 7

Regards,

Jagan.

[PrashantSangle]

Hi,

can you post your script??

Regards