topic Re: Combining Tables in QlikView

Combining Tables

Anonymous — Wed, 11 Jan 2017 16:24:20 GMT

Hey,

I have a problem with combining two tables which I have as QVD-Files.

For better understanding I create these two tables:

Table1:

LOAD * INLINE [

Auftr, KdNr

1, aa,

2, cc,

3, ee,

];

Table2:

LOAD * INLINE [

Auftr, LSNr, Date

1, xx, 10,

1, zz, 12,

3, yy, 11,

];

What I want to have is a new table with the following content:

AuftrNr,	KdNr,	LsNr,	Date
1,	aa,	--,	--,
1,	aa,	xx,	10,
1,	aa,	zz,	12,
2,	cc,	--,	--,
3,	ee,	--,	--,
3,	ee,	yy,	11,

I tried many different join as well as concatenate approaches but never got the correct result.

Can someone help me?

BR,

Emanuel

Re: Combining Tables

sunny_talwar — Wed, 11 Jan 2017 16:29:25 GMT

May be like this:

Table1:

LOAD * INLINE [

Auftr, KdNr

1, aa,

2, cc,

3, ee,

];

Table2:

LOAD * INLINE [

Auftr, LSNr, Date

1, xx, 10,

1, zz, 12,

3, yy, 11,

];

Join (Table1)

LOAD *

Resident Table2;

Concatenate (Table1)

LOAD DISTINCT Auftr,

KdNr

Resident Table1;

DROP Table Table2;

Re: Combining Tables

annafuksa1 — Wed, 11 Jan 2017 16:31:27 GMT

Use just join

I have attach example

Re: Combining Tables

surendraj — Wed, 11 Jan 2017 16:32:26 GMT

Hi sunny,

could you please elaborate above solution.its bit confusion for me..

Thanks,

surendra

Re: Combining Tables

sunny_talwar — Wed, 11 Jan 2017 16:33:45 GMT

Elaborate what? What part of the script do you not understanding? Its seems like a straight forward script, unless you have specific thing you don't understand

Best,

Sunny

Re: Combining Tables

sunny_talwar — Wed, 11 Jan 2017 16:35:57 GMT

Can be simplified to this

Table1:

LOAD * INLINE [

Auftr, KdNr

1, aa,

2, cc,

3, ee,

];

Join (Table1)

LOAD * INLINE [

Auftr, LSNr, Date

1, xx, 10,

1, zz, 12,

3, yy, 11,

];

Concatenate (Table1)

LOAD DISTINCT Auftr,

KdNr

Resident Table1;

DROP Table Table2;

Re: Combining Tables

Anonymous — Wed, 11 Jan 2017 16:37:46 GMT

Hey Anna,

your answer is the easiest to understand for me (sorry I'm a newby )

But I don't understand the meaning of "1 as flag", can you explain it for me.

Edit:

Unfortunately, the results are not correct..

Re: Combining Tables

sunny_talwar — Wed, 11 Jan 2017 16:40:07 GMT

But this doesn't seem like its meeting your requirement, is it?

vs. this

Or the required output had a typo?

Re: Combining Tables

annafuksa1 — Wed, 11 Jan 2017 16:43:19 GMT

its a flag from which table data are coming, you do not need to use it

Re: Combining Tables

Anonymous — Wed, 11 Jan 2017 16:44:16 GMT

You are right, I edited my question!

Re: Combining Tables

Anonymous — Wed, 11 Jan 2017 16:54:48 GMT

Thank you for your answer Sunny, this is what I was looking for

But one question, probably stupid one.

Why do I have to load these two columns of my table DISTINCT?

Re: Combining Tables

sunny_talwar — Wed, 11 Jan 2017 17:03:09 GMT

To get those null rows which won't created by a simple join

Re: Combining Tables

annafuksa1 — Wed, 11 Jan 2017 17:10:35 GMT

an I use them to avoid concatenation (if you have 2 exactly the same tables, Qlikview will concatenate them) and as I was planing to work on temporary tables (as inline are a little limited) I create this flags