topic Re: Cross join in designer mode in App Development

Cross join in designer mode

SC_Nshirke — Tue, 04 May 2021 20:12:12 GMT

Hi all,

I've below data layout, which is required to be printed in a graph as below.

Memberid	FirstName	Employer	StartMonth	EndMonth
12789716	John	xxx	2020-01	2021-03
7981161654	Scott	xxx	2020-04	2020-06
5654764	Jane	xxx	2021-01	2021-03

There are two ways I can accomplish this.

Create a table using cross join to add report month or
Using IterNo() on StartMonth and endMonth in the load script for Qlik to produce this information

In this case there will be a total of 21 rows for 3 users.

However, there is a problem in both approach. In both cases, we're providing data one way or the other to Qlik app during app refresh. If we take a millions of unique records , this approach takes very long time to load Qlik sense app.

I wanted to see if this could be achieved on the fly. So, I was thinking is there a way to replicate this in the Designer mode using Dimension/measure and some special function?

Any help would be super helpful.

Re: Cross join in designer mode

Dalton_Ruer — Wed, 05 May 2021 11:54:40 GMT

I don't believe you can use the Data Wizard to use the IntervalFunction but that is exactly what you need to solve this style of a problem. It essentially does your cross join for you. You feed it a Master Calendar that has every month, and then call the IntervalFuncion to match up the Month to the Start/End months for your employees.

That same algorithm works depending if you are trying to find things within months like this, or within hours so you can see who was staffing each hour. 15 minute increments or every minute.

I have attached a zip file that contains a bunch of files and different scenarios you can look to for help as well as see the other scenarios where IntervalMatch can be your friend.

Re: Cross join in designer mode

SC_Nshirke — Wed, 05 May 2021 15:20:29 GMT

Thanks @Dalton_Ruer for the suggestion! Let me take a look at this approach.