Creating new table from existing data

[Mark18]

Hi, 

I have a student data with admission date and leaving date values. I want to show the following visualisation:

For each student in each month how many days they stayed.

Example: 

Student id   admissionDate      leaving Date

S1                   1/1/2020                 10/2/2020

S2                   1/2/2020                 14/4/2020 

Required data table:

Student id     M1      M2    M3

S1.                    31.        10.    0

S2.                    29.         31.    14

M- month

[jwjackso]

Using this data

TempData:
Load * Inline [
StudentID,AdmissionDate,LeavingDate
S1,1/1/2020,10/2/2020
S2,1/2/2020,14/4/2020
];

Temp:
Load
min(AdmissionDate) as minDate,
max(LeavingDate) as maxDate

Resident TempData;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD
Date($(varMinDate) + IterNo() - 1) as CalDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Inner Join IntervalMatch(CalDate)

Load AdmissionDate
,LeavingDate
Resident TempData;

Calendar:
Load AdmissionDate
,LeavingDate
,CalDate
,If(Month(AdmissionDate) = Month(CalDate),1,Month(CalDate)-Month(AdmissionDate)+1) as Mth
Resident TempCalendar;

Drop Table TempCalendar;

Use the IntervalMatch function to assign calendar dates to the student information.  Create a new field Mth, which is the student attendance month (CalDate-AdmissionDate+1)

The measure in the pivot table is just count(Mth)

[Saravanan_Desingh]

One solution,

SET DateFormat='D/M/YYYY';

tab1:
Load * Inline [
StudentID,AdmissionDate,LeavingDate
S1,1/1/2020,10/2/2020
S2,1/2/2020,14/4/2020
];

Left Join(tab1)
LOAD *, Date(AdmissionDate+IterNo()) As Dt,'M'&AutoNumber(Month(AdmissionDate+IterNo()),StudentID) As Mn
Resident tab1
While AdmissionDate+IterNo()<=LeavingDate;

[Saravanan_Desingh · Accepted solution]

Output:

[Mark18 · Accepted solution]

Thanks it works. How can i make it start from M0?

[Saravanan_Desingh]

Try this,

'M'&AutoNumber(Month(AdmissionDate+IterNo()),StudentID)-1 As Mn

[Saravanan_Desingh · Accepted solution]

Sorry try this,

'M'&(AutoNumber(Month(AdmissionDate+IterNo()),StudentID)-1) As Mn