Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
your script looks ok, the interval match works.
Please explain your requirements a bit more in detail,
thanks
regards
Marco
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 ....
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
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....
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