Data Validation

[#1]

Hi,

I have the following query and I am trying to set some validation on it .

LOAD

     globalCallID_callId As CallID,

     callingPartyNumber As OriginalParty,

     originalCalledPartyNumber As DestinationParty,

     finalCalledPartyNumber As FinalParty,

     duration As Duration,

     //converttolocaltime(timestamp(dateTimeOrigination/86400+makedate(1970,1,1)),'UTC-05:00') As TimeStamp

     timestamp(dateTimeOrigination/86400+makedate(1970,1,1)+interval#(-5,'h')) as TimeStamp

     //dateTimeOrigination As TimeStamp

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where

    isnum(dateTimeOrigination)

********************************************************************************************************

Anybody can help me writting the right statements to set the the following validation

if the callingPartyNumber = 7 and the first three characters in callingPartyNumber = "810" then it is invalid record

if the length of the originalCalledPartyNumber =7 and the first three characters = "810 then it is invalid record"

if the length of the finalCalledPartyNumber = 7 and the the last three characters = "810" then it is invalid record.

if the originalCalledPartyNumber = "" and finalCalledPartyNumber = "" then it is invalid record

if duration = "0" then it is invalid record.

if the length of originalCalledPartyNumber  < 10 and callingPartyNumber = 4 then it is invalid record

One of those will be enough I'll do the rest..

Thxs

[#2]

I have one more question

I am wondering if it is possible to create a new field within the same table to load as follow: Name of the field extension.

If len(callingPartyNumber) = 4 then extention = callingPartyNumber

elseif len(originalCalledPartyNumber) = 4 then extension = originalCalledPartyNumber

else extension = finalCalledPartyNumber,

Sorry for being creative..

I am just trying to build the data module as it in my head

[swuehl]

Sure it is possible,

LOAD

...

if( len(callingPartyNumber) = 4, callingPartyNumber,

if( len(originalCalledPartyNumber)=4, originalCalledPartyNumber, finalCalledPartyNumber)) as extension,

...

from Table;

[#4]

This sounds to be working perfectly but I didn't understand the first line!!

Would you please explain to me how this statement work ..

like how did we set the callingPartyNumber to go to extention field when the length equal to 4?

[swuehl]

The first and the second line belong two one statement, consisting of an if() statement, where the else path again consists of a second if() statement. Just like you described above. Note that in a QV if() statement, you don't assign a value to a variable or field (in a load statement), the if() statement just returns a value. This is the difference to your if() statement block, where you use other assignment statements, like

extension = callingPartyNumber

Please refer to the Manual or look into the getting started sample files for more information.

[#6]

Thank you so much for your help.

I really appreciate that.

[#7]

Hey Swuehl,

After applying the if on all the log files I have gotten resaults under extention field that are  3 charachters or 7 characters..or more..

such as 250

             728

             71417816382346

and more like that while what I am looking for: an extention field or column that have the extentions and usually the extentions are 4 digits(characters).

Any advice?

Thanks,