Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenate or join ?

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

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

18 Replies
nagaiank
Specialist III
Specialist III

Concatenate

Anonymous
Not applicable
Author

Hi,

Use Concatenate:

Dec:

Load

Connection_No,

Date,

Name,

Bill,

Arrears,

Power_consumed,

Loss

From DEC_2009;


Concatenate


Jan:

Load

Connection_No,

Date,

Name,

Bill,

Arrears,

Power_consumed,

Loss

From JAN_2010;


Concatenate


Feb:

Load

Connection_No,

Date,

Name,

Bill,

Arrears,

Power_consumed,

Loss


From FEB_2010

Anonymous
Not applicable
Author

Use Concatenate as we have same columns across all tables,join is used when they are different columns.

Colin-Albert

The load will auto-concatenate the data to a single table containing the data from all source files because the field names are identical in each table being loaded. There is no need to add a concatenate command.

The table viewer should show one table containing the total number of rows fromDEC_2009, JAN_2010 and FEB_2010.

Anonymous
Not applicable
Author

yes qlikview auto concatenates for same columns across tables and to force concatenate we use concatenate syntax

Not applicable
Author

Hi Janaki,

No need to do Concatenate in your case as all the columns are same so, autoconcatenate will come into picture.

If you want to calculate sum(Connection_No) then it will give sum of total tables. But in case if you want to calculate each table, for example sum(Connection_No) for only Dec table, then you can't, to do that you need to add Flag for each table as I placed below. so you can use sum(Connection_No) per each table by using Set analysis.

Ex: To get DEC sum of Connection_No= sum({<Type={'Dec'}>}Connection_No).

To get Jan sum of Connection_No= sum({<Type={'Jan'}>}Connection_No).

To get Feb sum of Connection_No= sum({<Type={'Feb'}>}Connection_No).

Code: Just add one more column as below.

Dec:                                                      

Load

Connection_No,

Date,

Name,

Bill,

Arrears,

Power_consumed,

Loss,

'Dec' as Type

From DEC_2009;

Jan:

Load

Connection_No,

Date,

Name,

Bill,

Arrears,

Power_consumed,

Loss,

'Jan' as Type

From JAN_2010;

Feb:

Load

Connection_No,

Date,

Name,

Bill,

Arrears,

Power_consumed,

Loss,

'Feb' as Type

From FEB_2010

Thank you,

Not applicable
Author

Hi Raj ,

I tried your logic, I got 10000 records in each table .The results are coming as follows ,which is not true .Can you help me what to do here.  Screenshot (8).pngPFA

Not applicable
Author

Here I am not using sum FYI, Using Count.

Thanks,

JK

Not applicable
Author

Load

Connection_No,

Date,

month(Date) AS month,

year(Date) As year,

Name,

Bill,

Arrears,

Power_consumed,

Loss

From WHATEVERTHEFILE;

no matter how many loads you perform, can even go automatically using a loop., Qview will concatenate them automatically for you. and then in expression just refer to the month and year as follows:

in a chart. dimension, year,month and put sum(Power_consumed) in an expression.

from the text boxes

='Dec ' & chr(13) & SUM({1 <month={12},year={2014}>} Power_consumed)

                            or


='Dec ' & chr(13) & COUNT({1 <month={12},year={2014}>} DISTINCT Connection_No )


above for showing number of connections numbers in 2014 dec.