Cross join with a condition

[anagharao]

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

[avkeep01]

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;

[YoussefBelloum]

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 ?

[avkeep01]

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

[YoussefBelloum]

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

but smart approach here, thank you

[antoniotiman · Accepted solution]

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;

[antoniotiman]

Add in Script

SET DateFormat='DD MMM YYYY';

[teiswamsler]

Hi Anagha Rao

The Intervalmatch funktion will solved your requirements.

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

/Teis