Update field valu in load script

[#1]

I have a table with timestamps that I want to load, TimestampStart and TimestampEnd. Sometimes the TimestampEnd has a time that is before the time in TimestampStart, se examples in table below marked red.

I would like to correct this in the load script, so if TimestampStart is less than TimestampEnd, TimestampStart should be updated to TimestampEnd and TimestampEndt should be updated to TimestampStart, the values should change places. Hope anyone can help me to get the script correct.

TimestampStart	TimestampEnd
2010-01-01 00:10	2010-01-01 00:40
2010-01-01 00:10	2010-01-01 00:15
2010-01-01 00:15	2010-01-01 00:20
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:55
2010-01-01 00:30	2010-01-01 00:25
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 12:35
2010-01-01 00:45	2010-01-01 00:30
2010-01-01 00:30	2010-01-01 00:35
2010-01-01 00:30	2010-01-01 00:55
2010-01-01 00:30	2010-01-01 00:35

Best Regards

Mårten

[tresesco · Accepted solution]

Try like:

Load

     If( TimeStampStart>TimeStampEnd,  TimeStampEnd, TimeStampStart) as TimeStampStart,

     If( TimeStampStart>TimeStampEnd,  TimeStampStart, TimeStampEnd) as TimeStampEnd

From <>;

[sujeetsingh]

you can follow the nested if conditions as per Tresco.

[its_anandrjs]

Hi,

Load another table

Load

TimestampStart,

TimestampEnd,

if( TimestampStart > TimestampEnd, TimestampEnd,TimestampStart) as TimestampStartNew

From Source;

Ex:-

A:

LOAD

Timestamp#(TimestampStart,'YYYY-DD-MM hh:mm') as TimestampStart,

Timestamp#(TimestampEnd,'YYYY-DD-MM hh:mm') as TimestampEnd;

LOAD * Inline

[

TimestampStart,     TimestampEnd

2010-01-01 00:10,     2010-01-01 00:40

2010-01-01 00:10,     2010-01-01 00:15

2010-01-01 00:15,     2010-01-01 00:20

2010-01-01 00:30,     2010-01-01 00:35

2010-01-01 00:30,     2010-01-01 00:35

2010-01-01 00:30,     2010-01-01 00:55

2010-01-01 00:30,     2010-01-01 00:25

2010-01-01 00:30 ,    2010-01-01 00:35

2010-01-01 00:30 ,    2010-01-01 00:35

2010-01-01 00:30,     2010-01-01 12:35

2010-01-01 00:45 ,    2010-01-01 00:30

2010-01-01 00:30 ,    2010-01-01 00:35

2010-01-01 00:30 ,    2010-01-01 00:55

2010-01-01 00:30 ,    2010-01-01 00:35

];

NoConcatenate

B:

LOAD

TimestampStart,     TimestampEnd,

if( TimestampStart > TimestampEnd, TimestampEnd,TimestampStart) as TimestampStartNew,

if( TimestampStart > TimestampEnd, TimestampStart,TimestampEnd) as TimestampEndNew

Resident A;

DROP Table A;

Regards

Anand

[#5]

Thanks all, now it worked.

Best regards

Mårten