Interval match help

[Anonymous]

Hi Community people,

I need u r help in Interval Match, I'm struck to form the relation I'm attached the sample files in the attachments..

I want to form the intervalmatch between the requesterdate,start date,Enddate

I want the resultant table is one to one relationship

Please help me out of it

Miguel Angel Baeyens,tresescoJagan Mohan,Gysbert Wassenaar,swuehl

[MarcoWedel]

Hi,

your script looks ok, the interval match works.

Please explain your requirements a bit more in detail,

thanks

regards

Marco

[#3]

Indent_Header:

LOAD [Indent Number],

     [Requester Date],

     [Requester Position],

     [%Indent status code]

FROM

C:\Users\vipinvijayan1\Desktop\sreekanth\Indent.xls

(biff, embedded labels, table is [Sheet1$]);

HR:

LOAD

     Right('00000000' & ObjectID,8) as [Position ID],

     Replace([Start date],'.','/') as [Start date],

     Replace([End Date],'.','/') as [End Date],

     [ID of related object] as [Emp ID]

FROM

(ooxml, embedded labels, table is HRP1001);

BridgeTable:

Inner join

IntervalMatch([Requester Date])

Load

[Start date],

[End Date]

Resident HR;

To avoid synthetic key been formed , you need to inner join table containing start , end with interval match table ....

[MarcoWedel]

or maybe create an interval ID:

HR:
LOAD *,
    AutoNumberHash128([Start date], [End Date]) as IntervID;
LOAD
    Right('00000000' & ObjectID,8) as [Position ID],
    Replace([Start date],'.','/') as [Start date],
    Replace([End Date],'.','/') as [End Date],
    [ID of related object] as [Emp ID]
FROM
//
//(ooxml, embedded labels, table is HRP1001);
[http://community.qlik.com/servlet/JiveServlet/download/594718-121626/Copy%20of%20HRP1001.xlsx]
(ooxml, embedded labels, table is HRP1001);

Indent_Header:
LOAD [Indent Number],
    [Requester Date],
    [Requester Position],
    [%Indent status code]
FROM
//C:\Users\tss92046\Desktop\TEST\Indent.xls
//(biff, embedded labels, table is [Sheet1$]);
[http://community.qlik.com/servlet/JiveServlet/download/594718-121627/Indent.xls]
(biff, embedded labels, table is Sheet1$);

BridgeTable:
IntervalMatch([Requester Date])
Load
[Start date],
[End Date]
Resident HR;

Left Join (BridgeTable)
LOAD Distinct
  [Start date],
    [End Date],
    AutoNumberHash128([Start date], [End Date]) as IntervID
Resident BridgeTable;

DROP Fields [Start date], [End Date] From BridgeTable;

hope this helps

regards

Marco

[Anonymous]

Hi Marco,

Is the any possible to get the output in another method...

Exactly I want to get as 1). one to one relationship between indentnumber,requsterposition

                                      2).one to many as requesterposition,indentnumber....

[MarcoWedel]

Hi,

please explain how you think you can achieve a one to one relationship between indent number and requester position when you already have 1:n relations in your raw data:

thanks

regards

Marco