Two tables match char between range

[kstroupe]

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

[sunny_talwar]

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;

[swuehl]

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;

[swuehl · Accepted solution]

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

;

[kstroupe]

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