topic Re: Joining tables in QlikView

Joining tables

Anonymous — Fri, 25 Dec 2015 21:29:37 GMT

Good evening,colleagues!

I have simple example to solve my real problem.

In firtst load source i have three fields:

WareHouse,

Nomenclature

Date

Second:

Date

I need that every date from second soruce will be connected with every Nomenclature and every WareHouse (of cource, without dublicated dates, if some of them match in first source.

My necessary table:

WareHouse	Nomenclature	Date	Volume
1	33	01.01.2015	10
1	33	04.01.2015
1	33	05.01.2015
1	45	02.01.2015	20
1	45	04.01.2015
1	45	05.01.2015
1	46	03.01.2015	21
1	46	04.01.2015
1	46	05.01.2015

Re: Joining tables

sunny_talwar — Fri, 25 Dec 2015 21:39:33 GMT

Try this:

Main:

LOAD WareHouse,

Nomenclature,

Date,

Volume

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source1);

Join(Main)

LOAD Date as Date1

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source2);

Final:

LOAD WareHouse,

Nomenclature,

Date1 as Date

Resident Main;

Concatenate(Final)

LOAD WareHouse,

Nomenclature,

Date,

Volume

Resident Main;

DROP Table Main;

Re: Joining tables

maxgro — Fri, 25 Dec 2015 21:47:07 GMT

Main:

LOAD

WareHouse,

Nomenclature,

Date,

Volume

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source1)

Where len(Trim(WareHouse))>0

;

Tmp:

NoConcatenate load Distinct

WareHouse,

Nomenclature

Resident Main;

join (Tmp)

LOAD

Date

FROM Test.xlsx

(ooxml, embedded labels, table is Source2);

Concatenate (Main) LOAD * Resident Tmp;

DROP Table Tmp;

Re: Joining tables

Anonymous — Fri, 25 Dec 2015 21:47:32 GMT

Thanks, dear!!

I see some other result in your model:

Re: Joining tables

sunny_talwar — Fri, 25 Dec 2015 21:49:46 GMT

Your excel was pulling in more than 3 rows from source1 tab. try with the attached excel

Re: Joining tables

Anonymous — Sat, 26 Dec 2015 14:07:00 GMT

Thanks!!)

Re: Joining tables

Anonymous — Sat, 26 Dec 2015 14:07:15 GMT

Thanks, Sunny)

Re: Joining tables

sunny_talwar — Sat, 26 Dec 2015 14:19:12 GMT

Not a problem