Generating Timeseries data from time stamped data

[#1]

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.

	A	B	C	D
22/08/2017	1	0	0	0
23/08/2017	1	0	0	0
24/08/2017	1	0	0	0
25/08/2017	1	0	0	0
26/08/2017	0	1	0	0
27/08/2017	0	1	0	0
28/08/2017	0	1	0	0
29/08/2017	0	1	0	0
30/08/2017	0	1	0	0
31/08/2017	0	1	0	0
01/09/2017	0	1	0	0
02/09/2017	0	0	1	0
03/09/2017	0	0	1	0
04/09/2017	0	0	1	0
05/09/2017	0	0	1	0
06/09/2017	0	0	1	0
07/09/2017	0	0	0	1

Thanks,

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

[antoniotiman · Accepted solution]

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]

Or without Generic LOAD (remove last step)

[effinty2112]

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
1	22/08/2017	A
1	23/08/2017	A
1	24/08/2017	A
1	25/08/2017	A
1	26/08/2017	B
1	27/08/2017	B
1	28/08/2017	B
1	29/08/2017	B
1	30/08/2017	B
1	31/08/2017	B
1	01/09/2017	B
1	02/09/2017	C
1	03/09/2017	C
1	04/09/2017	C
1	05/09/2017	C
1	06/09/2017	C
1	07/09/2017	D
1	08/09/2017	D
1	09/09/2017	D
1	10/09/2017	D
1	11/09/2017	D
1	12/09/2017	D
1	13/09/2017	D
1	14/09/2017	D
1	15/09/2017	D
1	16/09/2017	D
1	17/09/2017	D
1	18/09/2017	D
1	19/09/2017	D

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

Cheers

Andrew

[#6]

Thank you Antonio,

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

Thanks again

[#7]

Thank you Andrew,

This also works, thank you