topic Re: Interval match help in QlikView

Interval match help

Anonymous — Mon, 25 Aug 2014 12:42:16 GMT

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,tresesco Jagan Mohan,Gysbert Wassenaar,swuehl

Re: Interval match help

MarcoWedel — Mon, 25 Aug 2014 14:04:48 GMT

Hi,

your script looks ok, the interval match works.

Please explain your requirements a bit more in detail,

thanks

regards

Marco

Re: Interval match help

Mon, 25 Aug 2014 17:36:35 GMT

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 ....

Re: Re: Interval match help

MarcoWedel — Mon, 25 Aug 2014 17:57:37 GMT

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

Re: Interval match help

Anonymous — Tue, 09 Sep 2014 12:56:41 GMT

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....

Re: Interval match help

MarcoWedel — Sun, 14 Sep 2014 13:33:13 GMT

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