Trying to create flag based on muliple load statements and criteria

[andrewmo]

I am attempting to create a flag in my load statement but the flag has some complex logic. Here is some pseudo code on what I am attempting to do:

CASE:

LOAD LOG_ID, IN_TIME, OUT_TIME, EMP_ID, ServDate, Location

FROM Case.qvd;

SERV:

LOAD EMP_ID, SERVICE

FROM Serv.qvd;

BLOCK:

LOAD SERVICE, DayOfWeek, WeekNum, StartDate, EndDate, StartTime, EndTime,BlockLoc

FROM Block.qvd;

CAL:

created calendar table with different date element to tie together

I need to create one final table using some join statements to create a flag. Flag logic needs to be like the following:

if CASE.ServDate between BLOCK.StartDate and BLOCK.EndDate

and CASE.IN_TIME between BLOCK.StartTime and BLOCK.EndTime

and CASE.Location = BLOCK.BlockLoc

and WeekDay(CASE.ServDate) = BLOCK.DayOfWeek

and CAL.WeekNum = BLOCK.WeekNum

THEN 1 ELSE 0

I have tried doing some joins to create a staging table with the elements needed, then build the if logic on the staging table but I can't seem to get it to work.

Anyone have any suggestions on the best way to accomplish this?

Thanks

[#2]

Sounds like you are on the right track.

If you want to create a qvw with the data mocked up using Inline tables I would be glad to look at it.

Let me know what you think,

- Stan