Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

h_prakash
Contributor

Interval Match Help In Qlik Sense

Hi Qlik Experts,

I have the Following Data Like this

   IntervalTable: 

ProjectNameStart DtEnd DtHr Per day
1john1/1/20155/31/20184
1Daniel3/31/20169/30/20187

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

1 Solution

Accepted Solutions
sebastiandperei
Valued Contributor

Re: Interval Match Help In Qlik Sense

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; 

10 Replies

Re: Interval Match Help In Qlik Sense

Maybe simply this would suffice for a single KPI :

Sum(Hr Per Day)

h_prakash
Contributor

Re: Interval Match Help In Qlik Sense

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

Re: Interval Match Help In Qlik Sense

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
Contributor

Re: Interval Match Help In Qlik Sense

Hi Bill Please Find the sample attachment.

sebastiandperei
Valued Contributor

Re: Interval Match Help In Qlik Sense

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
Contributor

Re: Interval Match Help In Qlik Sense

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
Valued Contributor

Re: Interval Match Help In Qlik Sense

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
New Contributor II

Re: Interval Match Help In Qlik Sense

Hi Hari,

Check with the following expression.

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


I hope this resolves the issue.


Regards,

Gagan

kaanerisen
Contributor III

Re: Interval Match Help In Qlik Sense

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

Untitled.png

Hope it helps...

Community Browser