Interval Match Help In Qlik Sense

[h_prakash]

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 Dt

Resident 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))

Please help..!!

[Anonymous]

Maybe simply this would suffice for a single KPI :

Sum(Hr Per Day)

[h_prakash]

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

[Anonymous]

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.

[h_prakash]

Hi Bill Please Find the sample attachment.

[sebastiandperei · Accepted solution]

Hi Hari, i have downloaded the file.

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 Date(Dates, 'MM/DD/YYYY') as Dates;

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

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

Interval:

Load * Inline [

[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)

Load StartDt,

     EndDt

Resident Interval;

Left join (Interval)

Load StartDt,

     EndDt,

     Dates

Resident BridgeTable;

Drop Table  [Master Calender],BridgeTable; 

[h_prakash]

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.

Please suggest...!!

[sebastiandperei]

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?

[gagan_bhasin]

Hi Hari,

Check with the following expression.

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

I hope this resolves the issue.

Regards,

Gagan

[kaanerisen]

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...