Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

etrotter
Contributor II

Bad Join on Date

Hello,

I am experincing an issue with one of my joins, three tables are joined off of Contract&Date and this works for the most part but there are some occurences where there isn't a date in my main table where there is a date in my side tables. So it drops the data from my side table wherever this occurs. I was wondering if there is a way I can build a field with all the contracts along with every possible date in the main table. It doesnt need to be populated but it needs to work to link the other two tables.

Let me know if I can clarify.

thanks,

Erica

1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: Bad Join on Date

Hi Erica,


In my opinion, if you do all dates per contract, it will multiply your data by a lot for each individual contract, even though that's not in the main table.

Over time that will start consuming a lot of disk space and memory, if you're doing a historic view.

Which specific need do you have for this case where there's a contract but no date associated to it?

If you put the values on a table, with the #Key being shown as a field, you'll just see the contract portion on the table, with the rest of the data empty.

Let's say you have this data:

Contract:

Load

Id & '|' & Date as #Key,

Id as ContractId,

Date as Contract.Date;

Load * Inline

[

Id,Date

1,42000

1,42001

2,42003

3,

];

Table2:

Load

ContractId & '|' & Date as #Key,

Id as Table2Id,

Date as Table2.Date;

Load * Inline

[

Id,Date,ContractId

1,42000,1

1,42001,2

555,42003,5

];

It will give you the following result:

#Key ContractId Contract.Date Table2Id Table2.Date
1|42000142000142000
1|42001142001  
2|42001 142001
2|42003242003  
3|3  
5|42003 55542003

Both tables are linked, but the second table "Table2" doesn't have a correspondent in contract table.

Can you add more to what you need?

1 Reply
felipedl
Valued Contributor III

Re: Bad Join on Date

Hi Erica,


In my opinion, if you do all dates per contract, it will multiply your data by a lot for each individual contract, even though that's not in the main table.

Over time that will start consuming a lot of disk space and memory, if you're doing a historic view.

Which specific need do you have for this case where there's a contract but no date associated to it?

If you put the values on a table, with the #Key being shown as a field, you'll just see the contract portion on the table, with the rest of the data empty.

Let's say you have this data:

Contract:

Load

Id & '|' & Date as #Key,

Id as ContractId,

Date as Contract.Date;

Load * Inline

[

Id,Date

1,42000

1,42001

2,42003

3,

];

Table2:

Load

ContractId & '|' & Date as #Key,

Id as Table2Id,

Date as Table2.Date;

Load * Inline

[

Id,Date,ContractId

1,42000,1

1,42001,2

555,42003,5

];

It will give you the following result:

#Key ContractId Contract.Date Table2Id Table2.Date
1|42000142000142000
1|42001142001  
2|42001 142001
2|42003242003  
3|3  
5|42003 55542003

Both tables are linked, but the second table "Table2" doesn't have a correspondent in contract table.

Can you add more to what you need?

Community Browser