Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How can I iterate a loaded table in script and compare values between lines/records?
There are in my table startDate, endDate and keyValue generated from startDate and some other fields.
Now if the startDate is one more than some previous endDate the keyValue should be the very same
as in the previous record
startDate endDate keyValue
20110325 20110327 20110325_plusotherstuff1
20110330 20110401 20110330_plusotherstuff2
20110402 20110403 20110330_plusotherstuff2 (same key as in previous record,
because start date 20110402 is the endDate 20110401 + 1
Thus keyValue binds together the continuous records.
T
Did you already try the inter-record functions?
Thanks. Inter-record Previous-function helps to some extent. But in my data
the chain of start and end dates can be long, say e.g. 10 records.
It's ok to write previous once or twice but previous(previous(previous(... no more
How can I write in a more elegant way the following ugly deep if structure in load.
In addition to the day condition two other fields must match.
if(field_1 <> previous(field_1), keyValue,
if(field_2 <> previous(field_2), keyValue,
if(startDate <> previous(endDate) + 1, keyValue,
// 1st match backwards found. Any more?
if(previous(field_1) <> previous(previous(field_1)), previous(keyValue),
if(previous(field_2) <> previous(previous(field_2)), previous(keyValue),
if(previous(startDate) <> previous(previous(endDate)) + 1, previous(keyValue),
// 2nd match backwards found. Any more?
if(previous(previous(field_1)) <> previous(previous(previous(field_1))), previous(previous(keyValue)),
if(previous(previous(field_2)) <> previous(previous(previous(field_2))), previous(previous(keyValue)),
if(previous(previous(startDate)) <> previous(previous(previous(endDate))) + 1, previous(previous(keyValue)),
// 3rd match backwards found. But there can be more.
previous(previous(previous(keyValue))))))))))))
as newKeyValue,
T