IntervalMatch help ?

[ganeshreddy]

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

[swuehl]

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.

[MarcoWedel]

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

hope this helps

regards

Marco

[MarcoWedel]

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

[ganeshreddy]

hi Swuehl,

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

Cheers,

ganesh

[ganeshreddy]

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

[swuehl]

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.