topic Re: Concatenate and Join in QlikView

Concatenate and Join

bmensing — Wed, 25 Nov 2020 16:16:04 GMT

Good day,

i've got three tables and i wanna merge them together. Let's say the tables are as follows

Table A:

# date time turnover(Product A)

234 01.07.2017 09:21 1000

236 04.08.2017 09:35 1500

Table B:

# date time turnover(Product B)

234 01.07.2017 09:21 500

236 04.08.2017 10:35 800

237 04.08.2017 11:32 400

Table C:

# date time turnover(Product C)

234 04.08.2017 10:35 100

239 13.09.2017 14:35 600

237 16.08.2017 11:32 400

and the endresult in a table Chart after selected the field '#' to 234 should look like this:

date time turnover(Product A) turnover(Product B) turnover(Product C)

01.07.2017 09:21 1000 500 -

04.08.2017 10:35 - - 100

Re: Concatenate and Join

lironbaram — Tue, 08 Aug 2017 13:08:51 GMT

your script should be

load * from table 1;

join

load * from table 2;

join

load * from table 3;

Re: Concatenate and Join

Anil_Babu_Samineni — Tue, 08 Aug 2017 13:18:50 GMT

What if you do direct load and create Table in front end

Even Concatenate Should work, This case

Re: Concatenate and Join

bmensing — Tue, 08 Aug 2017 13:20:23 GMT

but if i do so, i have one table with two date and two time fields like:

TableA.date

TableB.date

TableC.date

i just wanna have one field for the date and one field for the time

Re: Concatenate and Join

bmensing — Tue, 08 Aug 2017 13:23:07 GMT

Looks good but the dimension is refering to which field? like i said i have

three date fields

TableA.date

TableB.date

TableC.date

Re: Concatenate and Join

nilesh_gangurde — Tue, 08 Aug 2017 13:24:25 GMT

Hi,

please do not join - as the structure of all three tables seems to be similar.

use concate to append the table with Flag. for example :

load *,'Product A' as Flag

from table A;

Concatenate

load *,'Product B' as Flag from table B;

Concatenate

load *,'Product C' as Flag from table C;

This will create single table, then use flag to identify the products.

Regards,

Nilesh

Re: Concatenate and Join

PrashantSangle — Tue, 08 Aug 2017 13:24:53 GMT

Hi,

rename those field which you want to be same

for example

tableA.date as date

tableB.date as date

tableC.date as date

do similar for time field also

Regards

Re: Concatenate and Join

marcus_malinow — Tue, 08 Aug 2017 13:28:03 GMT

Do you have a QUALIFY statement in your code? If so, get rid of it, and you'll end up with a single date field.

Re: Concatenate and Join

Anil_Babu_Samineni — Tue, 08 Aug 2017 13:29:08 GMT

When you do with Concatenate / Join it just clubs to each table in single only. So, Don't require to specific which you expect. Simply use date as dimension. It clubs and showing?

Re: Concatenate and Join

effinty2112 — Tue, 08 Aug 2017 15:46:07 GMT

Hi Bernd,

Maybe

Table:

LOAD

'A' as Product,

LOAD * Inline [

#,date, time, turnover

234, 01.07.2017, 09:21, 1000

236, 04.08.2017, 09:35, 1500

];

LOAD

'B' as Product,

load * Inline [

#,date, time, turnover

234, 01.07.2017, 09:21, 500

236, 04.08.2017, 10:35, 800

237, 04.08.2017, 11:32, 400

];

LOAD

'C' as Product,

load * Inline [

#,date, time, turnover

234, 04.08.2017, 10:35,100

239, 13.09.2017, 14:35, 600

237, 16.08.2017, 11:32, 400

];

Then a pivot table

date	time	Product	A	B	C
01.07.2017	09:21		1000	500	-
04.08.2017	10:35		-	-	100

cheers

Andrew