topic Re: Data Validation in QlikView

Data Validation

Thu, 01 Mar 2012 15:32:18 GMT

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

Data Validation

swuehl — Thu, 01 Mar 2012 15:51:58 GMT

Maybe something along these lines

LOAD

...

if(

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

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

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

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

( duration = 0)

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

, 'INVALID'

, 'VALID' ) as RecordValidationStatus,

from ...;

Data Validation

Thu, 01 Mar 2012 15:54:47 GMT

Thxs,

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

Data Validation

swuehl — Thu, 01 Mar 2012 15:57:39 GMT

Try using the condition in your where clause:

...

Where

isnum(dateTimeOrigination) and not

(

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

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

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

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

( duration = 0)

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

);

Re: Data Validation

Thu, 01 Mar 2012 16:02:17 GMT

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

Any Idea?

Data Validation

swuehl — Thu, 01 Mar 2012 16:10:12 GMT

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

Re: Data Validation

Thu, 01 Mar 2012 16:20:04 GMT

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

Re: Data Validation

Thu, 01 Mar 2012 16:30:50 GMT

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

Data Validation

swuehl — Thu, 01 Mar 2012 16:33:44 GMT

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...

Re: Data Validation

Thu, 01 Mar 2012 16:44:27 GMT

That's true..

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

Thxs..

Re: Data Validation

Thu, 01 Mar 2012 19:24:38 GMT

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

Data Validation

swuehl — Thu, 01 Mar 2012 22:30:52 GMT

Sure it is possible,

LOAD

...

if( len(callingPartyNumber) = 4, callingPartyNumber,

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

...

from Table;

Re: Data Validation

Fri, 02 Mar 2012 15:18:29 GMT

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?

Data Validation

swuehl — Fri, 02 Mar 2012 16:42:12 GMT

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.

Re: Data Validation

Fri, 02 Mar 2012 17:11:30 GMT

Thank you so much for your help.

I really appreciate that.

Re: Data Validation

Tue, 06 Mar 2012 15:49:05 GMT

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,