Qlik Community

Ask a Question

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: 
Mark18
Contributor
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
jwjackso
Specialist II
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)

Untitled.png

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

Output:

commQV59.PNG

Mark18
Contributor
Contributor
Author

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

saran7de
Master
Master

Try this,

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

Sorry try this,

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