Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ganeshreddy
Contributor III

IntervalMatch help ?

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




6 Replies
MVP
MVP

Re: IntervalMatch help ?

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 ?

Intervalmatch (ServiceEventStartdate,ServiceEventEquipmentId)

LOAD CoverageStartDate,

          CoverageEndDate,

          EquipmentId

Resident Coverage;


hope this helps


regards


Marco

Re: IntervalMatch help ?

Hi,

one example could be:

QlikCommunity_Thread_167481_Pic3.JPG

QlikCommunity_Thread_167481_Pic2.JPG

QlikCommunity_Thread_167481_Pic1.JPG

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
Contributor III

Re: IntervalMatch help ?

hi Swuehl,

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

Cheers,

ganesh

ganeshreddy
Contributor III

Re: IntervalMatch help ?

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

MVP
MVP

Re: IntervalMatch help ?

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.

Community Browser