Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
venkatasreekant
Valued Contributor

Interval match help


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

Tags (1)
5 Replies

Re: Interval match help

Hi,

your script looks ok, the interval match works.

Please explain your requirements a bit more in detail,

thanks

regards

Marco

Not applicable

Re: Interval match help

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

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;

QlikCommunity_Thread_130598_Pic1.JPG.jpg

hope this helps

regards

Marco

venkatasreekant
Valued Contributor

Re: Interval match help

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

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:

QlikCommunity_Thread_130598_Pic2.JPG.jpg

thanks

regards

Marco