topic Re: Two tables match char between range in QlikView

Two tables match char between range

kstroupe — Wed, 05 Oct 2016 21:26:10 GMT

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

sunny_talwar — Wed, 05 Oct 2016 21:50:20 GMT

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;

Re: Two tables match char between range

swuehl — Wed, 05 Oct 2016 21:54:48 GMT

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

swuehl — Thu, 06 Oct 2016 06:08:25 GMT

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

kstroupe — Thu, 06 Oct 2016 15:11:42 GMT

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