Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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

maxgro
MVP
MVP

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;

Anonymous
Not applicable
Author

Thanks, dear!!

I see some other result in your model:

sunny_talwar

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

Anonymous
Not applicable
Author

Thanks!!)

Anonymous
Not applicable
Author

Thanks, Sunny)

sunny_talwar

Not a problem