topic Re: Cross join with a condition in QlikView

Cross join with a condition

anagharao — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data of an employee with the below fields

- EmpID

- EmpName

- Start week

- End week

Start and End week determining the period in which the employee was present in the company. I have a master calendar with weeks as the lowest level of granularity.

Is it possible to obtain records of the employee for each week he/she was in the company.

For example:

EmpID EmpName Start week EndWeek

1 Anagha 1 Jan 2017 5 Feb 2017

2 Anu 22 Jan 2017 12 Feb 2017

Should now be expanded for each week in between the start and the end week

EmpID EmpName Week

1 Anagha 1 Jan 2017

1 Anagha 8 Jan 2017

1 Anagha 15 Jan 2017

1 Anagha 22 Jan 2017

1 Anagha 29 Jan 2017

1 Anagha 5 Feb 2017

2 Anu 22 Jan 2017

2 Anu 29 Jan 2017

2 Anu 5 Feb 2017

2 Anu 12 Feb 2017

Thank you in advance.

Regards,

Anagha

Re: Cross join with a condition

avkeep01 — Fri, 01 Dec 2017 14:17:00 GMT

Hi Anagha,

Try

ExampleTable:

LOAD

EmpId,

EmpName,

[Start Week],

[End Week]

FROM [source];

LEFT JOIN (ExampleTable) LOAD

Date

RESIDENT [Master Calendar];

NOCONCATENATE LOAD

EmpId,

EmpName,

[Start Week],

[End Week],

Date

RESIDENT ExampleTable

WHERE DATE(Date ) >= DATE([Start Week]) AND DATE(Date) <= DATE([End Week])

AND Date = WEEKSTART(Date) // Apply this when you only want the first day of the week

;

DROP TABLE ExampleTable;

Re: Cross join with a condition

YoussefBelloum — Fri, 01 Dec 2017 15:07:41 GMT

hi, can please explain me somthing ?

ExampleTable:

LOAD

EmpId,

EmpName,

[Start Week],

[End Week]

FROM [source];

LEFT JOIN (ExampleTable) LOAD

Date

RESIDENT [Master Calendar];

on this first part you put, if there is no Date column on the first table, how can you do the left join with the calendar table ?

Re: Cross join with a condition

avkeep01 — Fri, 01 Dec 2017 15:12:37 GMT

Hi Youssef,

that is part the part of cross join.There is no need to have matching field names while making a join. Just cross the 2 tables. When creating a join without a connecting field al fields are multiplied by the amount of rows from both tables (so be careful with large dataset, it can take a lot of RAM).

When I multiply all rows, you'll get the Employee data for every date. The next step is reducing the data only for the row between the start and end date.

(then I realized that only the weekstarts where necessary so I wrote an extra line in the where clause, but should actually be in the Resident of the master calendar).

Re: Cross join with a condition

YoussefBelloum — Fri, 01 Dec 2017 15:20:16 GMT

Yess, cartesian join, completely forget about it..especially that i don't use it.

but smart approach here, thank you

Re: Cross join with a condition

antoniotiman — Fri, 01 Dec 2017 16:41:37 GMT

May be this

Temp:
LOAD * Inline [
EmpID, EmpName, StartWeek, EndWeek
1, Anagha, 1 Jan 2017, 5 Feb 2017
2, Anu, 22 Jan 2017, 12 Feb 2017
];
Concatenate
LOAD EmpID,EmpName,EndWeek,Date(StartWeek+Iterno()*7) as StartWeek
Resident Temp
While StartWeek+Iterno()*7 <= EndWeek;

Re: Cross join with a condition

antoniotiman — Fri, 01 Dec 2017 16:46:33 GMT

Add in Script

SET DateFormat='DD MMM YYYY';

Re: Cross join with a condition

teiswamsler — Mon, 04 Dec 2017 10:35:22 GMT

Hi Anagha Rao

The Intervalmatch funktion will solved your requirements.

http://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalMatch.htm‌

/Teis