Announcements
cancel
Showing results for
Did you mean:
Creator II

## Interval Match Help In Qlik Sense

Hi Qlik Experts,

I have the Following Data Like this

IntervalTable:

 Project Name Start Dt End Dt Hr Per day 1 john 1/1/2015 5/31/2018 4 1 Daniel 3/31/2016 9/30/2018 7

In the front end When a user select 2017 he wants to see how many hours John has or Daniel has.

This is Just a sample Data. In reality we have many rows in the above table.

I tried to use while loop generating dates between Start Dt and End Dt but it is taking more time to complete the load.

I see other option as interval match.

I tried like this

BridgeTable:

IntervalMatch (Dates)

 Load Start Dt,         End DtResident IntervalTable;

Dates field is coming from Master Calender.

But When I tried to create a KPI to sum of the Hrsperday in the year 2017 I am getting Calculation error.

The Expression is like this. Sum(Aggr(Sum(Hr Per Day), Project, Name, Dates))

1 Solution

Accepted Solutions
Specialist

Im not sure i have understood, but if you need to make the simple sum of hours, you need to join the Hr Per Day field next to the repeating of each date for each Name. So, your script must be.

Let vMinDate = Num(Date('01/01/2015'));

Let vMaxDate = Num(Date('01/31/2019'));

[Master Calender]:

Load \$(vMinDate) + IterNo()-1 as Dates

AutoGenerate 1 while \$(vMinDate) + IterNo()-1 <= \$(vMaxDate);

Interval:

[Project,Name,StartDt,EndDt,Hr Per Day

1, john, 1/1/2015, 5/31/2018, 4

1, Daniel, 3/31/2016, 9/30/2018, 7

];

BridgeTable:

IntervalMatch(Dates)

EndDt

Resident Interval;

Left join (Interval)

EndDt,

Dates

Resident BridgeTable;

Drop Table  [Master Calender],BridgeTable;

10 Replies
Anonymous
Not applicable

Maybe simply this would suffice for a single KPI :

Sum(Hr Per Day)

Creator II
Author

Hi Bill Thanks for responding. Since there is only one record in the first table it will give only 11.

Anonymous
Not applicable

Not sure I understand the context when you say there is only one record in the first table  - maybe if you could post a sample qvf that would help clarify.

Creator II
Author

Hi Bill Please Find the sample attachment.

Specialist

Im not sure i have understood, but if you need to make the simple sum of hours, you need to join the Hr Per Day field next to the repeating of each date for each Name. So, your script must be.

Let vMinDate = Num(Date('01/01/2015'));

Let vMaxDate = Num(Date('01/31/2019'));

[Master Calender]:

Load \$(vMinDate) + IterNo()-1 as Dates

AutoGenerate 1 while \$(vMinDate) + IterNo()-1 <= \$(vMaxDate);

Interval:

[Project,Name,StartDt,EndDt,Hr Per Day

1, john, 1/1/2015, 5/31/2018, 4

1, Daniel, 3/31/2016, 9/30/2018, 7

];

BridgeTable:

IntervalMatch(Dates)

EndDt

Resident Interval;

Left join (Interval)

EndDt,

Dates

Resident BridgeTable;

Drop Table  [Master Calender],BridgeTable;

Creator II
Author

Thank you sebastian for responding. I tried joining the Table as u suggested but it is taking forever to load and finally the reload is failing. Not sure because the Data is more around 153 Million Records.

Specialist

Ok, I see.

Tell me, what do you need? The minium detail leves you need? For example, if you have only monthly values the app will reduce a lot. Or if you need only a part of time...

The 153 M, are Interval Table?

Contributor II

Hi Hari,

Check with the following expression.

= Sum({ <  Year(Dates) = {2017} > } Hr Per Day)

I hope this resolves the issue.

Regards,

Gagan

Creator III

Hi Hari,

Your interval table has two records only. And simple sum expression will sum just for two records. If you want to sum up for all dates and names you should aggregate this values for all records.

sum(aggr(sum([Hr Per Day]),Dates,Name))

For 2017 Total Hr Per Day:

sum({<Dates={"=year(Dates)=2017"}>}aggr(sum({<Dates={"=year(Dates)=2017"}>}[Hr Per Day]),Dates,Name,[[Project]))

Hope it helps...