Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this function for all fields in the table:
if(Start_time-Previous(End_time) > '0',previous(End_time),End_time) as End_time,
The problem is that it always overwrites me the field, but I need a new line for that.
How can I solve this problem?
Thanks
Hi Alexander,
I think this will suit your requirements.
Data:
LOAD Key,
Name,
Start_Time,
End_Time
FROM
Data.xlsx
(ooxml, embedded labels, table is Tabelle1);
Temp_Data:
NoConcatenate
LOAD Key,
Name,
If(Start_Time > Previous(End_Time), Previous(End_Time)) AS Start_Time,
If(Start_Time > Previous(End_Time), Start_Time) AS End_Time
Resident Data ORDER BY Key, Name, Start_Time ASC;
Concatenate (Data)
LOAD Key,
Name,
Start_Time,
End_Time
Resident Temp_Data WHERE NOT IsNull(Start_Time);
DROP Table Temp_Data;
Please find the example attached.
Hope this helps you.
With kind regards,
Ronald
You mean that in your script you want to create new rows when specific conditions are met?
Can you post some sample input data and the expected output? Preferably in Excel format.
Yes that's what I want.
I have added a Excel Sheet.
Please give more detail about the below query:
What is the logic of these entries & if according to the dimension, one's end time is next start time then why we have void in between?
Hi Alexander,
I think this will suit your requirements.
Data:
LOAD Key,
Name,
Start_Time,
End_Time
FROM
Data.xlsx
(ooxml, embedded labels, table is Tabelle1);
Temp_Data:
NoConcatenate
LOAD Key,
Name,
If(Start_Time > Previous(End_Time), Previous(End_Time)) AS Start_Time,
If(Start_Time > Previous(End_Time), Start_Time) AS End_Time
Resident Data ORDER BY Key, Name, Start_Time ASC;
Concatenate (Data)
LOAD Key,
Name,
Start_Time,
End_Time
Resident Temp_Data WHERE NOT IsNull(Start_Time);
DROP Table Temp_Data;
Please find the example attached.
Hope this helps you.
With kind regards,
Ronald
Hi Ronald,
perfect! Thank you very much
Hi,
I have one problem now.
Sometimes this script is generating some lines at the start/end of every key.
I don't know why, it also takes for me random Start_time/End_time values.
How can I do this script only between the min and max Start_time?
Thanks
Hi Alexander,
That was of course to be expected. My bad. This should do it.
If(Key = Previous(Key), If(Start_Time > Previous(End_Time), Previous(End_Time))) AS Start_Time,
If(Key = Previous(Key), If(Start_Time > Previous(End_Time), Start_Time)) AS End_Time
Hi Ronald,
thank you so much!
I have added an "and" instead of the comma.
Then it worked