Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Is it possible to use IntervalMatch without creating synthetic key??

Hi,

Can we use intervalmatch without creating synthetic key??????

If so then plz explain me with example.

Thanks

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Use this enclosed file which contains two different example of IntervalMatch...

View solution in original post

9 Replies
MK_QSL
MVP
MVP

Please check below script...

DatesTable: 

LOAD * INLINE

  Name, Start, End 

  First, 01/01/2010, 31/01/2010  

  Second, 01/02/2010, 28/02/2010

  Third, 01/03/2010, 31/03/2010

  Four, 01/04/2010, 30/04/2010

];  

 

FactsTable:

LOAD * INLINE

  ID, Date, Amount 

  A, 03/02/2010, 100 

  B, 16/01/2010, 200

  C, 15/03/2010, 300

  E, 15/06/2010, 600

];  

 

FactsDates: 

INTERVALMATCH (Date) LEFT JOIN LOAD Start, End RESIDENT DatesTable; 

LEFT Join (FactsTable) LOAD * RESIDENT DatesTable;  

 

DROP TABLE DatesTable;

hic
Former Employee
Former Employee

First of all, the synthetic key that IntervalMatch creates is harmless and will not cause any problems. It is in fact a good solution to this data modelling problem. See IntervalMatch.

However, should you still want to remove it, you can do so by running a second pass (Load resident on a temporary table) and create a composite key from the Start and End fields. I do this quite often when I model Slowly changing dimensions. See more on IntervalMatch and Slowly Changing Dimensions

HIC

er_mohit
Master II
Master II

Try this code in script and reload it

LOAD * INLINE [

Desc, from-day, to-day

First, 1, 10

Second, 11, 20

Third, 21, 31

];

Emp:

LOAD * INLINE [

Name, day

A1, 1

A1, 5

A1, 2

A2, 7

A2, 15

A3, 25

];

Left join(Emp)

Intervalmatch(day)

LOAD [from-day], [to-day]

RESIDENT Category;

Left join (Emp)

LOAD * resident Category;

drop table Category;

Anonymous
Not applicable
Author

interval.PNG.png

This is the table box which contains above data

I cant able to figure out , whether data is populating correctly....

For ID A , it should show only FIRST and SECOND in name column...

MK_QSL
MVP
MVP

Dear Himanshu,

You should get below result... please check your script again..

Start End Name ID Date Amount
01/02/201028/02/2010SecondA03/02/2010100
01/01/201031/01/2010FirstB16/01/2010200
01/03/201031/03/2010ThirdC15/03/2010300
E15/06/2010600
Anonymous
Not applicable
Author

Hi Manish,

I verified many times....I am getting the same table box as I shown you previously by using your script....

MK_QSL
MVP
MVP

Use this enclosed file which contains two different example of IntervalMatch...

Anonymous
Not applicable
Author

Thanks Manish for ur great help...

MK_QSL
MVP
MVP

My pleasure...