4 Replies Latest reply: Oct 6, 2016 11:11 AM by Kim Stroupe

Two tables match char between range

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

• Re: Two tables match char between range

How about may be like this:

TableA:

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)

Max,

Serial

Resident TableA;

Left Join (TableB)

Resident TableA;

DROP Table TableA;

• Re: Two tables match char between range

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;

• Re: Two tables match char between range

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

;

• Re: Two tables match char between range

Thank you Stefan this is exactly what I needed.  You Rock!!