Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

himanshukr403
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
Not applicable

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

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

9 Replies
MK_QSL
Not applicable

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

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;

Henric_Cronström
Not applicable

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

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
Not applicable

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

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;

himanshukr403
Not applicable

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

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
Not applicable

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

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
himanshukr403
Not applicable

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

Hi Manish,

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

MK_QSL
Not applicable

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

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

himanshukr403
Not applicable

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

Thanks Manish for ur great help...

MK_QSL
Not applicable

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

My pleasure...