topic Re: IntervalMatch help ? in QlikView

IntervalMatch help ?

ganeshreddy — Mon, 08 Jun 2015 19:09:02 GMT

Hi All,

How to combine below highlighted two table with interval match

Main_Work_Order:

Load

ServiceEventEquipmentId,

ServiceEventStartdate,

.........................

From

[$(vQvdPath)\WorkOrder.qvd]

tmp_Work_Order:

Load

ServiceEventEquipmentId,

ServiceEventStartdate

resident Main_Work_Order;

Coverage:

Load

CoverageId,

EquipmentId,

CoverageStartDate,

CoverageEndDate,

Coverage Type,

WarrantyFlag,

custpaidFlag

from

[$(vQvdPath)\Coverage.qvd]

In the above scenario ServiceEventEquipmentId and EquipmentId are same, where as ServiceEventStartdate falls in between

CoverageStartDate and CoverageEndDate. how to use Interval match to combine(join) those tmp_Work_Order and Coverage.

Can anyone help me here.

Cheers,

Ganesh

Re: IntervalMatch help ?

swuehl — Mon, 08 Jun 2015 20:02:13 GMT

Maybe like this:

Main_Work_Order:

Load

ServiceEventEquipmentId,

ServiceEventStartdate,

.........................

From

[$(vQvdPath)\WorkOrder.qvd]

Coverage:

Load

CoverageId,

EquipmentId,

CoverageStartDate,

CoverageEndDate,

Coverage Type,

WarrantyFlag,

custpaidFlag

from

[$(vQvdPath)\Coverage.qvd]

INNER JOIN

INTERVALMATCH (ServiceEventStartdate, ServiceEventEquipmentId)

LOAD

CoverageStartDate,

CoverageEndDate,

EquipmentId as ServiceEventEquipmentId

RESIDENT Coverage;

Look also into the HELP, IntervalMatch with extended syntax.

Re: IntervalMatch help ?

MarcoWedel — Mon, 08 Jun 2015 20:18:20 GMT

Intervalmatch (ServiceEventStartdate,ServiceEventEquipmentId)

LOAD CoverageStartDate,

CoverageEndDate,

EquipmentId

Resident Coverage;

hope this helps

regards

Marco

Re: IntervalMatch help ?

MarcoWedel — Mon, 08 Jun 2015 23:28:02 GMT

Hi,

one example could be:

tmp_Work_Order:
LOAD *,
    AutoNumberHash128(ServiceEventEquipmentId,ServiceEventStartdate) as %WorkOrdID;
LOAD Ceil(Rand()*10) as ServiceEventEquipmentId,
    Date(Today()-Ceil(Rand()*30)) as ServiceEventStartdate
AutoGenerate 50;

Coverage:
LOAD *,
    AutoNumberHash128(EquipmentId,CoverageStartDate,CoverageEndDate) as %CoverID;
LOAD *,
    Date(CoverageStartDate+Ceil(Rand()*10)) as CoverageEndDate;
LOAD Ceil(Rand()*10) as EquipmentId,
    Date(Today()-Ceil(Rand()*30)) as CoverageStartDate
AutoGenerate 50;

tabLink:
Intervalmatch (ServiceEventStartdate,ServiceEventEquipmentId) 
LOAD CoverageStartDate, 
    CoverageEndDate, 
    EquipmentId as ServiceEventEquipmentId
Resident Coverage;

Left Join (tabLink)
LOAD Distinct
  AutoNumberHash128(ServiceEventEquipmentId,ServiceEventStartdate) as %WorkOrdID,
  ServiceEventEquipmentId,
  ServiceEventStartdate
Resident tabLink;

Left Join (tabLink)
LOAD Distinct
  AutoNumberHash128(ServiceEventEquipmentId,CoverageStartDate,CoverageEndDate) as %CoverID,
  ServiceEventEquipmentId,
  CoverageStartDate,
  CoverageEndDate
Resident tabLink;

DROP Fields ServiceEventEquipmentId, CoverageStartDate, CoverageEndDate, ServiceEventStartdate From tabLink;

hope this helps (although initially having missed to rename the EquipmentId field )

regards

Marco

Re: IntervalMatch help ?

ganeshreddy — Wed, 10 Jun 2015 14:29:23 GMT

hi Swuehl,

I think we need to take distinct values of ServiceEventStartdate (I am not sure ). please help me on this.

Cheers,

ganesh

Re: IntervalMatch help ?

ganeshreddy — Wed, 10 Jun 2015 14:32:22 GMT

Thanks Marco for this ..your approach wont work for my scenario, but your code clearly tells how to create Bridge table. a very good example creation with out data.

Cheers,

Ganesh

Re: IntervalMatch help ?

swuehl — Wed, 10 Jun 2015 14:38:07 GMT

I think we need to take distinct values of ServiceEventStartdate (I am not sure ). please help me on this.

Not sure what you want. Please post some sample data and your expected result.