Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ziabobaz
Contributor II

Create a table made of two (for each)

Hi,

I am struggling with the following: i need to combine two tables into ResultTable as below.

MainTable:

Date
2018-01-01
2018-01-02

CompanyTable:

CompanyAccount
Abank1
Abank2
Bbank2
Bbank3

ResultTable:

DateCompanyAccount
2018-01-01Abank1
2018-01-01Abank2
2018-01-01Bbank2
2018-01-01Bbank3
2018-01-02Abank1
2018-01-02Abank2
2018-01-02Bbank2
2018-01-02Bbank3

//I loaded all dates from my Calendar as a start:

MainTable:


LOAD

Date

RESIDENT [Calendar];


//Then for each Date I want to load Company-Account values from the CompanyTable

FOR EACH a in FieldValueList('Date')


Concatenate ([MainTable])

LOAD

Company,

Account

RESIDENT [CompanyTable];

   

NEXT a;



It does not work as I want - it just created the following table:

DateCompanyAccount
Abank1
Abank2
Bbank2
Bbank3
2018-01-02
2018-01-02
2018-01-02
2018-01-02

I guess it is wrong to use Concatenate here, than what is the correct way?

Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Create a table made of two (for each)

Why don't you just do a Join between the two tables

MainTable:

LOAD Date

RESIDENT [Calendar];

Join (MainTable)

LOAD Company,

    Account

RESIDENT [CompanyTable];

4 Replies
MVP
MVP

Re: Create a table made of two (for each)

Why don't you just do a Join between the two tables

MainTable:

LOAD Date

RESIDENT [Calendar];

Join (MainTable)

LOAD Company,

    Account

RESIDENT [CompanyTable];

roharoha
Valued Contributor III

Re: Create a table made of two (for each)

try

CARTHESIAN_TABLE:

LOAD * resident MainTable;

outer join

LOAD * resident CompanyTable:

drop table MainTable,CompanyTable;

ziabobaz
Contributor II

Re: Create a table made of two (for each)

That's correct
I complicated everything. Should have used join. 

Thank you!

ziabobaz
Contributor II

Re: Create a table made of two (for each)

Thank you, that's either working.