Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am breaking my head on following issue. I tried join and concatenate but in table viewer i can't see correct results.I
got three billing months data as.
Dec :
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From DEC_2009
Jan:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From JAN_2010
Feb:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From FEB_2010
Later on I need to calculate Connection number,Should I concatenate or Join.
experts can you help me on this issue please.
Thanks,
JK
Thank you Mario,
Distinct key word is helping but, the results are coming as follows .please cheak Total Connection number ,PFA
So total is the only number wrong? , what is your expression for this text box ?
you can use COUNT( TOTAL blabla DISTINCT yourfield)
Hi Mario,
This is what I am using as exp =count(distinct CONNECTION_NO) .however, in months Dec and Jan showing only 9999.Can I know the reason for that.
For the total =Count(Distinct TOTAL CONNECTION_NO)
for other months, what is your expression? , are u sure you don't have any selections in the document
hi mario,
my actual requirement is to count duplicate records and to show those records,but here i am plying with data whether its working or not...can you help me on my requirement...
Thanks,
JK
Hi JK,
Have you used: Count({<Type={'Dec'}>}Connection_No) ? to get December count.
Thank you,
As I don't know primary key for your tables, I just created a simple example for you.
I used AutoNumberHash128 with all my columns to show # of ocurrences. and a little text box showing some info about unique values etc.
Thanks a lot for you help Mario,I appreciate it,from your logic I can get no of duplicate records ,can you tel me how to show them.
The script:
temptable:
LOAD CODE,
CATEGORY,
YEAR,
MONTH,
VALUE,
AutoNumberHash128(CODE&CATEGORY&YEAR&MONTH&VALUE) AS MyKey
FROM
DATA688669.xlsx
(ooxml, embedded labels, table is Sheet1);
LOAD CODE,
CATEGORY,
YEAR,
MONTH,
VALUE,
AutoNumberHash128(CODE&CATEGORY&YEAR&MONTH&VALUE) AS MyKey
FROM
DATA688669_1.xlsx
(ooxml, embedded labels, table is Sheet1);
LOAD MyKey,Count(MyKey) AS numOcurrences
resident temptable GROUP BY MyKey;
THE TEXT BOX
='Number of Unique Values' & ' '&CHR(13)& COUNT({1 < numOcurrences={1}>} CODE)
Straight table chart: expression showing unique records : if (numOcurrences=1,numOcurrences)
Straight table chart: expression showing duplicate records : if (numOcurrences>1,numOcurrences)