Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Valued Contributor III

Re: Concatenate or join ?

Concatenate

18 Replies
nagaiank
Valued Contributor III

Re: Concatenate or join ?

Concatenate

neetha_p
Honored Contributor

Re: Concatenate or join ?

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

neetha_p
Honored Contributor

Re: Concatenate or join ?

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

Re: Concatenate or join ?

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.

neetha_p
Honored Contributor

Re: Concatenate or join ?

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

Not applicable

Re: Concatenate or join ?

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

Re: Concatenate or join ?

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

Re: Concatenate or join ?

Here I am not using sum FYI, Using Count.

Thanks,

JK

Not applicable

Re: Concatenate or join ?

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.

Community Browser