How to limit the dates over multiples tables

[QlikSenseUser2]

I have two different tables one is an excel file with dates starting in 2018 and the other is pulling from a repository folder with dates far back as 2016. I want to limit all dates to 2018 is there a way I can do that

[anthonyj]

Hi,

You can use a "where" statement to subset your data in the load script.

For example

data:

load

 ID,

 SomeDate

from RepositoryFolder\YourFile

where SomeDate > '01/01/2018';

[QlikSenseUser2]

This filtered the data during the load, but the date only changes on one field on the table depending on the table the date field is loaded from. I'm trying to get the filter to filter across all fields. The L/H is from one table and Miles comes for a different one. I'm trying to get a date  to filter both fields together 

[anthonyj · Accepted solution]

Hi,

I would be interested in knowing the association between these two tables. From your screen shot it looks like Truck# may be a key between the two tables. If you have the same Truck# with different dates then the combination of these two fields will be the unique identifier of each row in your data but without seeing an example I'll be guessing here. You can create a composite key in the load script to join on.

data:

load

 autonumber(Truck# & RepoDate) as _JoinKey,
[Truck#]    as RepoTruck,
RepoDate,
[L/H]

from RepositoryFolder\YourFile

where SomeDate > '01/01/2018';

join(data)
load
 autonumber(Truck# & ExcelDate) as _JoinKey,
[Truck#]    as ExcelTruck,
ExcelDate,
Miles
from ExcelFolder\ExcelFile;

Assuming their are dates in the first table that aren't in the second and vice versa, and that you want to keep all of the dates then this full join will keep all of these.
Finally, you can create a master date field by using the coalesce() function.

FinalOutput:
load 

coalesce(RepoTruck, ExcelTruck)  as [Truck#],
date(coalesce(RepoDate, ExcelDate)) as Date,

[L/H],

Miles

Resident Data;

drop table data;