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

If statement in load script using fields from two other tables

Hello all,

So, I have a table:

FactTable:

FactDate,

DepartmentID,

EmployeeID,

...

FROM Table1

And then I have two other tables which tells me if DepartmentID and/or EmployeeID should be excluded from analysis reports in certain periods of time:

DepartmentList:

Exc_DepartmentID,

dStartDate,

dEndDate

FROM DepartmentExclusionList;

EmployeeList:

Exc_EmployeeID,

eStartDate,

eEndDate

FROM EmployeeExclusionList;

My wish is to create a new field in my fact table called 'Exclude' which contains the values 'Yes' or 'No'.

The value must be 'Yes' if either DepartmentID OR EmployeeID can be matched in their respective exclusion lists AND if the FactDate falls in between the start date and end date in the given list.

I'm thinking something like this:

If(

DepartmentID= Exc_DepartmentID AND (FactDate>= dStartDate AND FactDate<= dEndDate)

OR

EmployeeID = Exc_EmployeeID AND (FactDate>= eStartDate AND FactDate<= eEndDate)

,'Yes','No') as Exclude

Note: The end dates can be null, meaning that the exclusion is ongoing (until an end date is specified in the list).

So how can I achieve my goal? I'm thinking that I somehow need to do some joining and use resident loads, but I have very little idea of how to do it.

1 Solution

Accepted Solutions
marcus_malinow
Partner - Specialist III
Partner - Specialist III

hi Martin,

I'd suggest IntervalMatch for this.

DepartmentExclusions:

IntervalMatch(FactDate, DepartmentID)

LOAD

dStartDate,

dEndDate,

Exc_DepartmentID

FROM DepartmentExclusionList;


LEFT JOIN (FactTable)

LOAD

FactDate,

DepartmentID,

1 as DepartmentExclusion

RESIDENT DepartmentExclusions;


DROP TABLE DepartmentExclusions;


Repeat for ExployeeExclusionList

   

View solution in original post

2 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

hi Martin,

I'd suggest IntervalMatch for this.

DepartmentExclusions:

IntervalMatch(FactDate, DepartmentID)

LOAD

dStartDate,

dEndDate,

Exc_DepartmentID

FROM DepartmentExclusionList;


LEFT JOIN (FactTable)

LOAD

FactDate,

DepartmentID,

1 as DepartmentExclusion

RESIDENT DepartmentExclusions;


DROP TABLE DepartmentExclusions;


Repeat for ExployeeExclusionList

   

Anonymous
Not applicable
Author

Hi Marcus

At first I couldn't get it to work, but after reading a bit about the IntervalMatch function and then altering your code a little bit, I was able to make it work.

Thanks!