# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for
Search instead for
Did you mean:
Contributor

## Creating new table from existing data

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

6 Replies
Specialist II

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)

Master

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;``````
Master

Output:

Contributor
Author

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

Master

Try this,

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

Sorry try this,

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