Load script interval

[QV85]

Hi ALL

I get such data [Table 1] and I need to convert them into the form shown in Table 2
I need to do an interval every minute and assign VALUE and Part to this interval
Anyone have any idea how to do this ??

[sunny_talwar]

Here is one way to do this

Table:
LOAD * INLINE [
    Part, CreationDate, Value
    A, 09/24/2020 10:00, 1
    A, 09/24/2020 10:03, 0
    A, 09/24/2020 10:06, 1
    B, 09/24/2020 10:04, 1
    B, 09/24/2020 10:06, 0
    B, 09/24/2020 10:09, 1
];

tmpTable:
LOAD TimeStamp(MinDate + MakeTime(0, IterNo()-1)) as CreationDate,
	 Round((MinDate + MakeTime(0, IterNo()-1))*24*60) as CreationDateNum
While TimeStamp(MinDate + MakeTime(0, IterNo()-1)) <= MaxDate;
LOAD Min(CreationDate) as MinDate,
	 Max(CreationDate) as MaxDate
Resident Table;

Left Join (tmpTable)
LOAD Distinct Part
Resident Table;

Left Join (tmpTable)
LOAD Part,
	 Round(CreationDate*24*60) as CreationDateNum,
	 Value
Resident Table;

DROP Table Table;

FinalTable:
NoConcatenate
LOAD Part,
	 CreationDate,
	 If(Part = Previous(Part), If(Len(Trim(Value)) = 0, Peek('Value'), Value), Value) as Value
Resident tmpTable
Order By Part, CreationDateNum;

DROP Table tmpTable;

[QV85]

Thank you very much
unfortunately there is no data in FinalTable
3 Fields, 0 Rows

[MarcoWedel · Accepted solution]

Hi,

another example might be:

 

table1:
LOAD Part,
     Timestamp#(CreationDate,'MM/DD/YYYY hh:mm') as CreationDate,
     Value 
Inline [
    Part, CreationDate, Value
    A, 09/24/2020 10:00, 1
    A, 09/24/2020 10:03, 0
    A, 09/24/2020 10:06, 1
    A, 09/24/2020 10:10, 0
    B, 09/24/2020 10:04, 1
    B, 09/24/2020 10:06, 0
    B, 09/24/2020 10:09, 1
    C, 09/24/2020 09:50, 1
    C, 09/24/2020 09:55, 0
];

Join
LOAD  Fieldvalue('Part',IterNo()) as Part, *
While IterNo() <= FieldValueCount('Part');
LOAD  Timestamp#(Timestamp(MinCreationDate+(IterNo()-1)*'00:01','MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as CreationDate
While Timestamp#(Timestamp(MinCreationDate+(IterNo()-1)*'00:01','MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') <= MaxCreationDate;
LOAD  Timestamp(Min(Fieldvalue('CreationDate',RecNo())),'MM/DD/YYYY hh:mm') as MinCreationDate,
      Timestamp(Max(Fieldvalue('CreationDate',RecNo())),'MM/DD/YYYY hh:mm') as MaxCreationDate
AutoGenerate FieldValueCount('CreationDate');

NoConcatenate
table2:
LOAD Part,
     CreationDate,
     If(IsNull(Value) and Part=Previous(Part), Peek(Value), Value) as Value
Resident table1
Order By Part, CreationDate;

DROP Table table1;

hope this helps

regards

Marco

[QV85]

Thanks a lot Marco
this is exactly what I was looking for
works perfect

[MarcoWedel]

you're welcome

please close your thread

thanks

Marco