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: 
Smeenakshi23
Creator II
Creator II

Extended Interval Match

Hello

I am having a calendar table linked with the fact table,

 

Calendar :

Left keep ( fact)

dt_key,// linked with fact

date

from calendar.

Department Table:

load Automnumbet startdate enddate as key 1 / not correct syntax.. I just typed

CD // Linked with fact,

Class // Linked with fact

start date,

end date,

performance

 from department table

I am performing interval match.

interval table ;

interval  match( date)

load start date,

end date resident department;

temp :

load  date,Automnumbet startdate enddate as key 1

  resident  Interval table:

 

When executing this i am getting circular reference loop. On removing the link bewtwee the department table and fact table i am not getting performance filed selected,

 

So i tried implementing like this

 

interval match(date, performance)

laod startdate, endate,performance

resident department;

temp :

load  date,Automnumbet startdate enddate as key 1

  resident  Interval table:

 

 

is the above this correct

 

 

 

 

 

Labels (1)
2 Solutions

Accepted Solutions
Vegar
MVP
MVP

Try something like this:

SET DateFormat='YYYY-MM-DD';

Dept:
LOAD * , AutoNumberHash128( StartDate, EndDate, CA, Class ) AS %Dept  inline[ 
StartDate, EndDate, CA, Class, Performance
2019-01-01, 2019-11-30, A, 1, Good
2019-12-01, 2019-12-31, A, 1, Bad
2020-01-01, 2020-01-31, A, 1, Bad
2020-02-01, 2020-02-03, A, 1, Good
2019-01-01, 2019-11-30, B, 1, Bad
2019-12-01, 2019-12-03, B, 1, Average
2020-01-01, 2020-01-31, B, 1, Good
2020-01-01, 2020-02-03, B, 1, Good
];

Fact:
LOAD 
	Date, 
	AutoNumberHash128(Date, CA, Class) as %Fact,
	CA, Class, Dimension 
inline [
Date,  CA, Class, Dimension
2019-10-30, A,1, ABC
2019-12-30, A,1, BCD
2020-01-30, A,1, CDE
2019-02-02, A,1, DEF
2019-10-30, B,1, DEF
2019-12-30, B,1, CDE
2020-01-30, B,1, BCD
2019-02-02, B,1, ABC
2020-01-06, A,1, CDE
2019-12-02, A,1, DEF
2019-01-30, B,1, DEF
2019-02-30, B,1, CDE

];

Link:
IntervalMatch(Date, CA, Class)
LOAD 
	StartDate, 
	EndDate, 
	CA, 
	Class 
Resident Dept;

LEFT join 
LOAD 
	AutoNumberHash128( StartDate, EndDate, CA, Class ) as %Dept,
	AutoNumberHash128(Date, CA, Class) as %Fact
Resident 
	Link;
Drop field CA, Class, Date, StartDate, EndDate From Link;
Drop field CA, Class From Fact;


Calendar:
LOAD 
MonthName(Date) as YearMonth,
dayname(Date) as Date;
LOAD 
today()-365+RecNo() as Date
AutoGenerate 365
Where exists (Date, today()-365+RecNo()) // Just to limit the amuount of days in my example
;

 

It will give you this datamodel:

image.png

 

See attached qvw

View solution in original post

Smeenakshi23
Creator II
Creator II
Author

Thanks a lot for your example

View solution in original post

12 Replies
Vegar
MVP
MVP

I think you might be leaving something out of your description.  The field Performance shouldn't affect your data modelling as you are describing it, why do you want to include it as a parameter to your interval match? 

I tried to follow your description of your data model using pen and paper and found the reason to your loop to be the original intervalmatch table, named IntervalMatch in my picture. Try to drop it and you should be fine.

20200203_084317.jpg

 

Smeenakshi23
Creator II
Creator II
Author

sorry if my post wasnt clear.

 

The field CD and class from departement table are linked with the fact table.. so i renamed both the field inorder to remove the connection.

 

So wen  i remove the connection between dea[rtment table and fact table the performance field from department table are not populating data properly

 

 

 

Smeenakshi23
Creator II
Creator II
Author

e==getting loop error even dropping it

Vegar
MVP
MVP

Can you post a screenshot of your data modell? It might be easier to understand?

Vegar
MVP
MVP

I don't know your data, but normally you would need to have a connection between department and transactions, preferably you have some kind of department ID that you can connect to the department dimension.

Through which tables are your loop? Is it through the transactions table? On which fields and table? Could you explain a bit more?

Smeenakshi23
Creator II
Creator II
Author

Screenshot_20200203-141542_Gallery.jpg

 

Smeenakshi23
Creator II
Creator II
Author

If you see fact table there is a ditted link

Vegar
MVP
MVP

I suggest you get rid of the direct  connection between Department and your transactions. Pass through your Class and CA fields through the interval match instead, if they identifies your relationship between  the two. 

 

Smeenakshi23
Creator II
Creator II
Author

I am sorry.. I dint get it. can u please share me an example