topic Re: How to acheive comparison using between and one or more other columns. in QlikView

How to acheive comparison using between and one or more other columns.

Thu, 23 Jul 2015 21:07:08 GMT

I have two tables and would like to join on two columns with equality and other using between. similar to the one in SQL as below. (excuse me for any syntax errors). I tried with IntervalMatch which does not seems to work because of extra check required for ID column.

INSERT INTO FINAL_TABLE (FromDate, ToDate, ID, Value, Type)

SELECT FromDate, ToDate, ID, Value, Type

FROM TABLE_A

UNION ALL

SELECT TABLE_B.FromDate, NULL as ToDate, TABLE_B.ID, TABLE_A.Value, TABLE_B.Type

FROM TABLE_B

LEFT JOIN TABLE_A ON TABLE_A.ID = TABLE_B.ID AND TABLE_B.FromDate BETWEEN TABLE_A.FromDate AND TABLE_A.ToDate

Table structures as following:

Table_A:

FromDate, ToDate, ID, Value, Type

01/01/2015, 01/30/2015, 1, 10, TableA

01/01/2015, 01/30/2015, 2, 11, TableA

02/01/2015, 02/15/2015, 2, 15, TableA

Table_B:

FromDate, ID, Type

01/15/2015, 1, TableB

01/15/2015, 2, TableB

02/15/2015, 2, TableB

02/15/2015, 3, TableB

Table to be created

Final_Table:

FromDate, ToDate, ID, Value, Type

01/01/2015, 01/30/2015, 1, 10, TableA

01/01/2015, 01/30/2015, 2, 11, TableA

02/01/2015, 02/15/2015, 2, 15, TableA

01/15/2015, NULL, 1, 10, TableB

01/15/2015, NULL, 2, 11, TableB

02/15/2015, NULL, 2, 15, TableB

02/15/2015, NULL, 3, NULL, TableB

Re: How to acheive comparison using between and one or more other columns.

vardhancse — Fri, 24 Jul 2015 04:35:14 GMT

Table_A:

FromDate, ToDate, ID, Value, Type

01/01/2015, 01/30/2015, 1, 10, TableA

01/01/2015, 01/30/2015, 2, 11, TableA

02/01/2015, 02/15/2015, 2, 15, TableA

Left join(Table_A)

FromDate, ID, Type

01/15/2015, 1, TableB

01/15/2015, 2, TableB

02/15/2015, 2, TableB

02/15/2015, 3, TableB

Based on FromDate both the tables will be joined

Re: How to acheive comparison using between and one or more other columns.

Fri, 24 Jul 2015 13:23:15 GMT

Please observed the date values in the FromDate in TABLE_B they are not same as TABLE_A or in other words it is to be compared between TABLE_A FROM/TO Dates to find pick appropriate TABLE_A.Value.

Re: How to acheive comparison using between and one or more other columns.

sunny_talwar — Fri, 24 Jul 2015 14:44:02 GMT

This:

Script:

Table_A:

LOAD * Inline [

FromDate, ToDate, ID, Value, Type

01/01/2015, 01/30/2015, 1, 10, TableA

01/01/2015, 01/30/2015, 2, 11, TableA

02/01/2015, 02/15/2015, 2, 15, TableA

];

Table_B:

LOAD FromDate as Date,

ID,

Type as Type2

Inline [

FromDate, ID, Type

01/15/2015, 1, TableB

01/15/2015, 2, TableB

02/15/2015, 2, TableB

02/15/2015, 3, TableB

];

Join(Table_B)

IntervalMatch:

IntervalMatch (Date, ID)

LOAD FromDate,

ToDate,

Resident Table_A;

NewTable:

NoConcatenate

LOAD FromDate,

ToDate,

ID,

Value

Resident Table_A;

Join(NewTable)

LOAD FromDate,

ToDate,

ID,

Date,

Type2

Resident Table_B;

Concatenate(Table_A)

LOAD Date as FromDate,

Null() as ToDate,

ID,

Type2 as Type,

Value

Resident NewTable;

DROP Tables NewTable, Table_B;

Re: How to acheive comparison using between and one or more other columns.

Fri, 24 Jul 2015 15:04:49 GMT

Sunny that was excellent,That was exactly what i wanted. I was not aware IntervalMatch() would take multiple parameters.

Re: How to acheive comparison using between and one or more other columns.

sunny_talwar — Fri, 24 Jul 2015 16:08:00 GMT

It does . I recently figured that out also.

I am glad we are learning something new each day.

Best,

Sunny