Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Enthusiasts,
I am here to ask how can i achieve the below output.
student_ID , Exam_ID , Date , Status, Expected Output
1,101, 12-01-2021, Done, -
2,102,12-02-2021, Done,-
2, 103,13-03-2022,'Cancelled',-
3, 104, 14-02-2015,'Done', -
3,105,14-03-2016,'Cancelled',-
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years
I want the logic to check if the previous 'Student_ID' is the same , then check if the status is not equal to Cancelled,
then Get the difference between the date and previous date.
I have used the following formula , but i could not get the expected output.
If(previous(student_ID)= student_ID and Status<>'Cancelled',
Interval(Date - Previous(Date),'d'));
Please help me correct the syntax if it is not the correct one.
Thank you so much.
Try this,
SET DateFormat='DD-MM-YYYY';
tab1:
LOAD *, If(Peek(student_ID)=student_ID,
Interval(Date - Peek(Date),'d')) As Output
Where Status<>'Cancelled'
;
LOAD * INLINE [
student_ID,Exam_ID,Date,Status,Expected Output
1,101,12-01-2021,Done,
2,102,12-02-2021,Done,
2,103,13-03-2022,Cancelled,
3,104,14-02-2015,Done,
3,105,14-03-2016,Cancelled,
3,106,16-02-2015,Done,2 DAYS
3,107,16-02-2022,Planned,7 years
];
Try this,
SET DateFormat='DD-MM-YYYY';
tab1:
LOAD *, If(Peek(student_ID)=student_ID,
Interval(Date - Peek(Date),'d')) As Output
Where Status<>'Cancelled'
;
LOAD * INLINE [
student_ID,Exam_ID,Date,Status,Expected Output
1,101,12-01-2021,Done,
2,102,12-02-2021,Done,
2,103,13-03-2022,Cancelled,
3,104,14-02-2015,Done,
3,105,14-03-2016,Cancelled,
3,106,16-02-2015,Done,2 DAYS
3,107,16-02-2022,Planned,7 years
];
Output:
Hello the formula works fine on this date, But i don't know why it does not work on the actual data.
I have also checked if its the date format issue, by just giving the first part of the formula , like below,
If (Peek(Student_ID)= Student_ID, 'S,'D')
but it doesn't show me anything in the output column,Just blank values . Is there something else to consider while using Peek ? The field i am using to get the previous value in the original data is a 10 digit number.
thank you so much.
Hello Thank you for the formula, It worked after ordering it by student_ID and Date,
I have one more question regrding the same logic.
Now that we see output, I have thousands of records in the table and i want to see all the output >0 in the front.
to do that i have ordered by output in the table. Although i cannot see the other exam_id of that paticular student_ID. for example:
If i order by output here in the table,
I get values as follows
Student_ID Exam_ID , Output
3,106,2
3,197,2557
1,101,-
2,102,-
3,104,-
I would like to see only the values of 3 as one of its output is greater than 0.
i.e.,
Expected Output:
3,104,-
3,106,2
3,107,2557
Is this possible ?
Please help me with the query
thank you so much for your support.
Much appreciated.:D
Try this,
tab1:
LOAD * INLINE [
Student_ID,Exam_ID,Output
3,106,2
3,197,2557
1,101,-
2,102,-
3,104,-
];
Left Join(tab1)
LOAD Student_ID, If(Concat(DISTINCT Output)='-','X','I') As Flag
Resident tab1
Group By Student_ID
;
tab2:
NoConcatenate
LOAD *
Resident tab1
Where Flag='I';
Drop Table tab1;
Output:
Student_ID , Exam_ID , Date , Status, Expected Output
1,101, 12-01-2021, Done, -
2,102,12-02-2021, Done,-
2, 103,13-03-2022,'Cancelled',-
3, 104, 14-02-2015,'Done', -
3,105,14-03-2016,'Cancelled',-
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years
1,101, 12-01-2021, Done, -
2,102,12-02-2021, Done,-
2, 103,13-03-2022,'Cancelled',-
3, 104, 14-02-2015,'Done', -
3,105,14-03-2016,'Cancelled',-
3,106,16-02-2015, 'Done', 2 DAYS
3,107,16-02-2022,'Planned', 7 years