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

[swuehl]

Maybe something along these lines

LOAD

...

if(

(len(callingPartyNumber) = 7 and left(callingPartyNumber,3) = '810' )

or

(len(originalCalledPartyNumber) =7 and left(originalCalledPartyNumber,3) = '810')

or

(len(finalCalledPartyNumber) = 7 and right(finalCalledPartyNumber,3) = '810')

or

( len(originalCalledPartyNumber) = 0 and len(finalCalledPartyNumber) =0 )

or

( duration = 0)

or

(len(originalCalledPartyNumber)  < 10 and callingPartyNumber = 4)

, 'INVALID'

, 'VALID' )  as RecordValidationStatus,

..

from ...;

[#3]

Thxs,

When I said invalid record, I mean I dont want it to be uploaded.

[swuehl · Accepted solution]

Try using the condition in your where clause:

...

Where

    isnum(dateTimeOrigination) and not

(

(len(callingPartyNumber) = 7 and left(callingPartyNumber,3) = '810' )

or

(len(originalCalledPartyNumber) =7 and left(originalCalledPartyNumber,3) = '810')

or

(len(finalCalledPartyNumber) = 7 and right(finalCalledPartyNumber,3) = '810')

or

( len(originalCalledPartyNumber) = 0 and len(finalCalledPartyNumber) =0 )

or

( duration = 0)

or

(len(originalCalledPartyNumber)  < 10 and callingPartyNumber = 4)

);

[#5]

Sounds better, I see some changes noww but instead of having less data I am having more!!

Any Idea?

[swuehl]

Can't imagine more records, could you maybe upload your sample app and the log file?

[#7]

I have uploaded the file on a previous replay..I wasn't able to attach files in here.

[#8]

Let me know if you got it and thank you so much for your help.

[swuehl]

If I remove the new condition to the where clause, I get 524 CallIDs, using my suggested part 197.

So it seems to filter something...

[#10]

That's true..

I dont know how did I get the previous results..

Thxs..