Cross Join, where

[danfuer123]

I have a date table that contains every day of the year.
I would like to cross join that with time slices in another table that contains ID, start and end date!

It is an easy crossjoin with a where clause in SQL, but how to solve that in Qlikview??

I have a solution that creates a cross Join in a temp table.

On have to filter in a second step and drop the temp table, but im not happy about that.

We are talking about 20 Months * 30 Days * 200000 DataSets = 120,000,000 Tuples in a temp table.

Is there a solution to use the  where clause in the first step while crossing the tables??

The interval join cannot be used here!

DO You have ideas? 😉

/**************************************************************************************************************************/

EventLog:
LOAD * Inline [
Time, Event, Comment
00:00, 0, Start of shift 1
01:18, 1, Line stop
02:23, 2, Line restart 50%
04:15, 3, Line speed 100%
08:00, 4, Start of shift 2
11:43, 5, End of production
];

/**************************************************************************************************************************/
OrderLog:
LOAD * INLINE [
Start, End, Order
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];

/**************************************************************************************************************************/
Result:
noConcatenate

load Time, Event
Resident EventLog;

Join

load Start, End
Resident OrderLog;

drop table OrderLog;
drop table EventLog;

/**************************************************************************************************************************/
NoConcatenate

Result_final:
load *
resident Result
Where ( Time >Start and Time < End);

drop table Result;

EXIT SCRIPT;

[edwin]

perhaps this will help:
Solved: Re: Moving a field from one table to another based... - Qlik Community - 1777543

there is a suggestion there that uses intervalmatch