Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dickelsa
Not applicable

Show 'previous' activity

Hello all,

I am dealing with an issue at the moment.

currently I am building a dashboard that should function as a timeline. with this I mean: I have a few activities that are linked to a startdate. the startdate from the second activity however, is the enddate of the first activity.

the following example should explain it:

given is the following table:

activitydate
talking1-1-2013
drinking20-1-2013
eating22-2-2013
running12-5-2014
sleeping2-6-2014

now the activity 'Talking' starts at 1-1-2013 and ends at 20-1-2013, because then the activity 'Drinking' starts. The activity 'Drinking' ends on 22-2-2013 because then the activity 'eating' starts and so on.

so when I should click the date 15-1-2013, the activity 'talking' should be activated, and so on

i made this working with the following script:

Activity1:

NoConcatenate Load DISTINCT Date,

Month(Date) as CalendarMonth,

Day(Date) as CalendarDay,

Week(Date) as CalendarWeek,

Year(Date) as CalendarYear,

IF (Ceil(num(Month(Date)))/3 > 0, 'Q' & Ceil(num(Month(Date))/3), null()) AS CalendarQuarter,

If( IsNull( Activiteit ), Peek( Activiteit ), Activiteit ) as Activiteit,

If( IsNull(Afdeling), Peek(Afdeling),Afdeling) as Afdeling,

If( IsNull(Rij), Peek(Rij),Rij) as Rij,

If( IsNull(Soort), Peek(Soort),Soort) as Soort

Resident TempFactTable1

Where Date<>''

Order By Date ; // so that above values can be propagated downwards

now i have the range of the dates linked to my activities.

now comes the tricky part:

I want to get the 'previous' activity with the startdate.

How do i do this?

I currently have this, but I cant figure out what to do.

Can you guys help me?

Thanks in advance,

Dick

6 Replies
Not applicable

Re: Show 'previous' activity

Hi,

Quick question. How do you determine the order. Like Talking  is 1, drinking is 2, eating is 3, etx..

Thanks

AJ

dickelsa
Not applicable

Re: Show 'previous' activity

Hello

I dont 'number' the activties, but if you want to number them autonumber(activtiy) as activitynr

greets

dick

Not applicable

Re: Re: Show 'previous' activity

I created a table with StartDate and EndDate as per your logic. So now you can get your results based on which ever date you want to use.

Hope it helps

Thanks

AJ

santharubban
Not applicable

Re: Show 'previous' activity

You try Peek(Date) as pervious date

i think i will help u

dickelsa
Not applicable

Re: Show 'previous' activity

Problem is, my table is not a straight table but a crosstable in loadscript.

Not applicable

Re: Show 'previous' activity

Can you attach a sample source file.

Also why can't you make it a straight table and then apply this logic.