Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Generating Timeseries data from time stamped data

If I had the following data table,

TempData:
LOAD * inline
[ID,Event,EventDate
1, A, 22/08/2017
1, B, 26/08/2017
1, C, 02/09/2017
1, D, 07/09/2017
]
;

What would be the best way to generate a report that would show the current status for each day between the min and max date?

Conceptually, I want to create an output like this.

   

ABCD
22/08/20171000
23/08/20171000
24/08/20171000
25/08/20171000
26/08/20170100
27/08/20170100
28/08/20170100
29/08/20170100
30/08/20170100
31/08/20170100
01/09/20170100
02/09/20170010
03/09/20170010
04/09/20170010
05/09/20170010
06/09/20170010
07/09/20170001

Thanks,

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Peter,

maybe like this

Temp:
LOAD * inline [ID,Event,EventDate
1, A, 22/08/2017
1, B, 26/08/2017
1, C, 02/09/2017
1, D, 07/09/2017 ]
;
Temp1:
LOAD ID,Event,EventDate,Alt(Peek(EventDate)-1,EventDate) as EventDateEnd
Resident Temp
Order By ID,EventDate Desc;
Drop Table Temp;
Temp2:
LOAD ID,Event,Date(EventDate+IterNo()-1) as EventDate
Resident Temp1
While EventDate+IterNo()-1 <= EventDateEnd;
Drop Table Temp1;
Generic
LOAD EventDate,Event,ID
Resident Temp2;
Drop Table
Temp2;

Regards,

Antonio

View solution in original post

6 Replies
Anil_Babu_Samineni

Can you try this one? and create report.

Sample:

LOAD * inline [

ID,Event,EventDate

1, A, 22/08/2017

1, B, 26/08/2017

1, C, 02/09/2017

1, D, 07/09/2017

];

Calendar:

LOAD

    Date(EventDate) AS EventDate;

Load Date(MinDate + IterNo() -1 ) AS EventDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(EventDate) AS MinDate,

    Max(EventDate) AS MaxDate

RESIDENT Sample;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Master III
Master III

Hi Peter,

maybe like this

Temp:
LOAD * inline [ID,Event,EventDate
1, A, 22/08/2017
1, B, 26/08/2017
1, C, 02/09/2017
1, D, 07/09/2017 ]
;
Temp1:
LOAD ID,Event,EventDate,Alt(Peek(EventDate)-1,EventDate) as EventDateEnd
Resident Temp
Order By ID,EventDate Desc;
Drop Table Temp;
Temp2:
LOAD ID,Event,Date(EventDate+IterNo()-1) as EventDate
Resident Temp1
While EventDate+IterNo()-1 <= EventDateEnd;
Drop Table Temp1;
Generic
LOAD EventDate,Event,ID
Resident Temp2;
Drop Table
Temp2;

Regards,

Antonio

antoniotiman
Master III
Master III

Or without Generic LOAD (remove last step)

effinty2112
Master
Master

Hi Peter,

Here's another way, this time using IntervalMatch:

TempData:

LOAD * inline

[ID,Event,EventDate

1, A, 22/08/2017

1, B, 26/08/2017

1, C, 02/09/2017

1, D, 07/09/2017

];

MinEventDate:

LOAD min(EventDate) as MinEventDate

Resident TempData;

Let vMinDate = Peek('MinEventDate',0,'MinEventDate');

DROP Table MinEventDate;

DescData:

LOAD *,

if(RecNo()=1,Date(Floor(Today())), Date(Peek(EventDate) -1)) as EndDate

Resident TempData Order by ID,EventDate desc;

DROP Table TempData;

For i = 1 to FieldValueCount('ID')

Result:

LOAD

FieldValue('ID',$(i)) as ID,

Date($(vMinDate)+RecNo()-1) as Date

AutoGenerate Floor(Today()) - $(vMinDate)+1;

Next i;

Left Join(DescData)

IntervalMatch(Date, ID) LOAD EventDate,EndDate, ID Resident DescData;

Left Join(Result)

LOAD ID, Date, Event Resident DescData;

Giving:

ID Date Event
122/08/2017A
123/08/2017A
124/08/2017A
125/08/2017A
126/08/2017B
127/08/2017B
128/08/2017B
129/08/2017B
130/08/2017B
131/08/2017B
101/09/2017B
102/09/2017C
103/09/2017C
104/09/2017C
105/09/2017C
106/09/2017C
107/09/2017D
108/09/2017D
109/09/2017D
110/09/2017D
111/09/2017D
112/09/2017D
113/09/2017D
114/09/2017D
115/09/2017D
116/09/2017D
117/09/2017D
118/09/2017D
119/09/2017D

You can put the above into a pivot table if you prefer of course.

Cheers

Andrew

Not applicable
Author

Thank you Antonio,

This works perfectly for me. Now to apply it at scale!

Thanks again

Not applicable
Author

Thank you Andrew,

This also works, thank you