Previous Value

[Manasareddy09]

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. 

[Saravanan_Desingh · Accepted solution]

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
]; 

[Saravanan_Desingh · Accepted solution]

Output:

[Manasareddy09]

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. 

[Manasareddy09]

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

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output:

[Manasareddy09]

Thank you for the reply, The problem is i cant do this in the backend because half of the formula is in the front end as well. Below is the clear discription.

I have a problem which I have been trying to solve from past few weeks but I could not get any answers. I have written the scenario below with a sample data set. 

Please help me if you have some time. Your time would be much appreciated.

Data looks like below 

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 to see if there are Duplicate Exams which have been scheduled within 6,7,8 etc months (Depending on the user INPUT)

Right now , I have the following formula:

If(PEEK(Student_ID = Student_ID and Peek(Status)= Done and Status <> ‘Cancelled’ ,

Interval(Date - Peek(Date),’d') ) a output;

Order By Date 

And then in the front end, 

If(Date > Today(),

Output <= $(vPmonths)*30,

Output & ‘Days'

)

* $(vPmonths) is the input given by the user in the iNPUT VARIABLE CHART, 

The output looks like below after sorting it by date in Pivot table

Student_ID , Exam_ID , Date , Status, Output 

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',-

But I want to see all the entries of the students whose output is >=0, which looks like this 

Student_ID , Exam_ID , Date , Status, Output 

3,106,16-02-2015, 'Done', 2 DAYS

3,107,16-02-2022,'Planned', 7 years

3,105,14-03-2016,'Cancelled',-

The difference is that here I have all entries of Student_ID = 3 and nothing else. 

Please let me know if this is possible to do in Qliksense . 

Thank you so much, 

Best regards,

Bandari.