Extracting single consecutive row in a loop from a loaded table

[HeshamKhja]

Hi!

I have a table [HIRE TABLE] with PERSON_ID, HIRE_DATE and TERMINATION_DATE as columns.

I want to create a new table that has PERSON_ID, ACTIVE_DATE, ACTIVE_FLAG.
The ACTIVE_DATE is a date field that has dates starting from HIRE_DATE till TERMINATION_DATE for each employee.
The ACTIVE_FLAG will be accordingly be 1 for all employees.

What I am facing difficulty figuring out is how to have each person with specific dates range starting from his HIRE_DATE till TERMINATION_DATE.

I hope it is understandable, and this is my attempt to solve it:

---------------------------------------------------------------------------------------------------------------------

for each PERSON_ID in [Hire Date]   //Looping for each ID in the table

StartAndEndDates:  // Table that will get the unique date for each person (Didn't work :s)
LOAD
HIRE_DATE AS FirstOrderDate,
TERMINATION_DATE_NEW AS LastOrderDate
RESIDENT [Hire Date];
//WHERE PERSON_ID = IterNo();

LET vFirstDate = NUM(PEEK('FirstOrderDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastOrderDate', 0, 'StartAndEndDates'));

temp_GeneratedDates:
LOAD
PERSON_ID,
Date('$(vFirstDate)' + IterNo() - 1) AS ReferenceDate,
'1' as ACTIVE_FLAG
RESIDENT [Hire Date]
WHILE '$(vLastDate)' >= '$(vFirstDate)' + IterNo() + 1;

DROP TABLE StartAndEndDates;
next

[StarinieriG · Accepted solution]

Hi,

maybe something like this (file attached)?

[HeshamKhja · Accepted solution]

Thank you so much.

Your solution helped me to come with the solution.

I ended with this:
-------------------------------------------------------------------------------------------------------------------

// Iterating on each ID to extract the needed values (ID, HIRE_DATE, TERMINATION_DATE)
FOR p = 1 to NoOfRows('Hire Date')

// Creating Variables to hold the extracted needed values from HIRE DATE Table (ID, HIRE_DATE, TERMINATION_DATE)
// Using the PEEK() function to get the appropriate first and last date so we can generate the dates appropriately.
LET PERSON_ID = Peek('PERSON_ID', $(p)-1,'Hire Date');
LET vFirstDate = NUM(PEEK('HIRE_DATE', $(p)-1, 'Hire Date'));
LET vLastDate = NUM(PEEK('TERMINATION_DATE_NEW', $(p)-1, 'Hire Date'));

// Creating table [GeneratedDates] which we'll save the needed values in
GeneratedDates:
LOAD
$(PERSON_ID) AS PERSON_ID,
Date('$(vFirstDate)' + IterNo() - 1) AS ReferenceDate, //Incrementally generating dates
'1' as ACTIVE_FLAG
AutoGenerate 1 // Limit autogeneration to 1 time
WHILE '$(vLastDate)' >= '$(vFirstDate)' + IterNo() -1; //Generating dates only within the needed range

NEXT

-------------------------------------------------------------------------------------------------------------------

Thanks a lot again!