Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ??
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
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;
Thank you very much
unfortunately there is no data in FinalTable
3 Fields, 0 Rows
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
Thanks a lot Marco
this is exactly what I was looking for
works perfect
you're welcome
please close your thread
thanks
Marco