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;
