Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andreyfcdk91
New Contributor III

Joining tables

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:

    

WareHouseNomenclatureDateVolume
13301.01.201510
13304.01.2015
13305.01.2015
14502.01.201520
14504.01.2015
14505.01.2015
14603.01.201521
14604.01.2015
14605.01.2015
1 Solution

Accepted Solutions

Re: Joining tables

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;


Capture.PNG

7 Replies

Re: Joining tables

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;


Capture.PNG

MVP
MVP

Re: Joining tables

1.png

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;

andreyfcdk91
New Contributor III

Re: Joining tables

Thanks, dear!!

I see some other result in your model:

Re: Joining tables

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

andreyfcdk91
New Contributor III

Re: Joining tables

Thanks!!)

andreyfcdk91
New Contributor III

Re: Joining tables

Thanks, Sunny)

Re: Joining tables

Not a problem

Community Browser