topic Re: Concatenate or join ? in QlikView

Concatenate or join ?

Sat, 03 Jan 2015 18:25:09 GMT

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,

Re: Concatenate or join ?

nagaiank — Sat, 03 Jan 2015 18:51:22 GMT

Concatenate

Re: Concatenate or join ?

Anonymous — Sat, 03 Jan 2015 19:03:38 GMT

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

Re: Concatenate or join ?

Anonymous — Sat, 03 Jan 2015 19:05:40 GMT

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

Re: Concatenate or join ?

Colin-Albert — Sat, 03 Jan 2015 19:24:09 GMT

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.

Re: Concatenate or join ?

Anonymous — Sat, 03 Jan 2015 19:26:56 GMT

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

Re: Concatenate or join ?

Sat, 03 Jan 2015 19:59:13 GMT

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,

Re: Concatenate or join ?

Sun, 04 Jan 2015 05:49:50 GMT

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. PFA

Re: Concatenate or join ?

Sun, 04 Jan 2015 05:51:11 GMT

Here I am not using sum FYI, Using Count.

Thanks,

Re: Concatenate or join ?

Sun, 04 Jan 2015 06:08:04 GMT

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)

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

above for showing number of connections numbers in 2014 dec.

Re: Concatenate or join ?

Sun, 04 Jan 2015 06:29:52 GMT

Thank you Mario,

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

Re: Concatenate or join ?

Sun, 04 Jan 2015 06:33:24 GMT

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

you can use COUNT( TOTAL blabla DISTINCT yourfield)

Re: Concatenate or join ?

Sun, 04 Jan 2015 06:51:28 GMT

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.

Re: Concatenate or join ?

Sun, 04 Jan 2015 06:54:54 GMT

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

Re: Concatenate or join ?

Sun, 04 Jan 2015 07:10:02 GMT

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,

Re: Concatenate or join ?

Sun, 04 Jan 2015 07:20:59 GMT

Hi JK,

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

Thank you,

Re: Concatenate or join ?

Sun, 04 Jan 2015 08:00:15 GMT

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.

Re: Concatenate or join ?

Sun, 04 Jan 2015 08:18:02 GMT

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.

Re: Concatenate or join ?

Sun, 04 Jan 2015 16:41:07 GMT

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)