Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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
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?
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
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.
what do u want the exact output?
Hi,
Are you couning Distinct TransactionID or TransactionID Count?
For Distinct:
Count(Distinct TransactionsID) // Returns 5
Count(TransactionID) // Will give 7
Regards,
Jagan.
Hi,
can you post your script??
Regards