Concatenate or join ?

[#1]

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

[#2]

Thank you Mario,

Distinct key word is helping but, the results are coming as follows .please cheak Total Connection number ,PFA

[#3]

So total is the only number wrong? , what is your expression for this text box ?

you can use COUNT( TOTAL  blabla  DISTINCT yourfield)

[#4]

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.

[#5]

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

[#6]

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

[#7]

Hi JK,

Have you used: Count({<Type={'Dec'}>}Connection_No) ? to get December count.

Thank you,

[#8]

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.

[#9]

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.

[#10]

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)