Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have two tables and need to do an IntervalMatch but can't because my data is a character and not a number. Can this be done in Qlik?
Table A
Min_Serial Max_Serial Task Complied
123A-0001 123A-0009 A Y
123A-0010 123A-9999 A N
Table B
Serial
123A-0003
In SQL I can do if(Serial between Min_Serial and Max_Serial, Serial, 0)
Thank you in Advance
Kim
If you want to follow the direction using the relational operators, I think follows and precedes won't consider equal values as true, so you might need to add a check for equality:
RESULT:
NOCONCATENATE
LOAD * Resident TableA
WHERE
(Serial follows Min_Serial and Serial precedes Max_Serial)
or Serial = Min_Serial or Serial = Max_Serial
;
How about may be like this:
TableA:
LOAD *,
SubField(Min_Serial, '-', 1) as Serial,
SubField(Min_Serial, '-', 2) as Min,
SubField(Max_Serial, '-', 2) as Max;
LOAD * INLINE [
Min_Serial, Max_Serial, Task, Complied
123A-0001, 123A-0009, A, Y
123A-0010, 123A-9999, A, N
];
TableB:
LOAD Serial as Serial_Main,
SubField(Serial, '-', 1) as Serial,
SubField(Serial, '-', 2) as Key;
LOAD * INLINE [
Serial
123A-0003
];
Left Join (TableB)
IntervalMatch(Key, Serial)
LOAD Min,
Max,
Serial
Resident TableA;
Left Join (TableB)
LOAD *
Resident TableA;
DROP Table TableA;
Another option:
TableA:
LOAD * INLINE [
Min_Serial, Max_Serial, Task, Complied
123A-0001, 123A-0009, A, Y
123A-0010, 123A-9999, A, N
];
TableB:
JOIN
LOAD * INLINE [
Serial
123A-0003
];
RESULT:
NOCONCATENATE
LOAD * Resident TableA
WHERE Serial follows Min_Serial and Serial precedes Max_Serial;
DROP Table TableA;
If you want to follow the direction using the relational operators, I think follows and precedes won't consider equal values as true, so you might need to add a check for equality:
RESULT:
NOCONCATENATE
LOAD * Resident TableA
WHERE
(Serial follows Min_Serial and Serial precedes Max_Serial)
or Serial = Min_Serial or Serial = Max_Serial
;
Thank you Stefan this is exactly what I needed. You Rock!!