How to duplicate employee data based on Start & End dates

[Anonymous]

Hi,

I have a table with Employee ID, Start Date & End Date.

I wanted to duplicate the records at date level to be started from Start Date and End till End Date of respective employees.

Sample Input data available in 'Sheet1' sheet.

Sample output required showed in 'Output need' sheet for one Employee.  I'm beginner to Qlik, so pls help me in this.

Regards,

Mohan.Y

[maxgro · Accepted solution]

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Directory;

Source:

LOAD EMPLOYEE_CODE,

     START_DATE,

     END_DATE

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

MinMaxDate:

Load Min(START_DATE) as MinDate, Max(END_DATE) as MaxDate resident Source;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Calendar:

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

join (Source) LOAD Date Resident Calendar;

Table:

NoConcatenate LOAD *

Resident Source

where Date >= START_DATE and Date <= END_DATE;

DROP Table MinMaxDate;

DROP Table Calendar;

DROP Table Source;

[#3]

Hi,

Alternate wate to duplicate the records:

EmpTable:

Load * inline [

EMPLOYEE_CODE, START_DATE, END_DATE

1234, 15-Nov-12, 2-Feb-14

1235, 10-Jul-12, 2-Feb-14

1236, 11-Nov-13, 2-Feb-14];

Load *

Date(Date#(START_DATE, 'DD-MMM-YY') + IterNo() -1) as Date

Resident EmpTable

while Date(Date#(START_DATE, 'DD-MMM-YY') + IterNo() -1) <= Date#(END_DATE, 'DD-MMM-YY');

DROP TABLE EmpTable;