topic Re: How to duplicate employee data based on Start & End dates in QlikView

How to duplicate employee data based on Start & End dates

Anonymous — Mon, 03 Feb 2014 10:54:49 GMT

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

Re: How to duplicate employee data based on Start & End dates

maxgro — Mon, 03 Feb 2014 11:13:30 GMT

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;

Re: How to duplicate employee data based on Start & End dates

Mon, 03 Feb 2014 11:57:33 GMT

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;