topic Re: Eliminate data in Load script in QlikView

Eliminate data in Load script

Wed, 05 Feb 2014 14:44:10 GMT

Hi,

I want to eliminate data containing "Total:" in both column A and Column B. Can I do this in Edit Script? Also how to eliminate blanks in the data. Attached is the data.

AML_Master:

LOAD A,

FROM

C:\Users\Country.txt

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

Thank you in advance,

Jasmine

Re: Eliminate data in Load script

Gysbert_Wassenaar — Wed, 05 Feb 2014 14:48:50 GMT

AML_Master:

LOAD A,

FROM

C:\Users\Country.txt

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

WHERE NOT Wildmatch(A,'*Total:') AND NOT Wildmatch(B,'*Total:') ;

Re: Eliminate data in Load script

puttemans — Wed, 05 Feb 2014 15:07:04 GMT

And for the blanks, you add

AML_Master:

LOAD A,

len(A) as LengthA,

len(B) as LengthB

FROM

C:\Users\Country.txt

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

WHERE NOT Wildmatch(A,'*Total:') AND NOT Wildmatch(B,'*Total:');

AML_Master2:

LOAD *,

Resident AML_Master

WHERE LengthA <>0 OR LengthB<>0;

DROP TABLE AML_MASTER;

This will remove everything with a blank in either A or B, if you need it in both, then you need to use the AND operator.

Take care, length will be 0 if you have nothing in the cell, if you have e.g. a '-', it will be 1. Best to check the contents of LengthA/B first.

Re: Eliminate data in Load script

Wed, 05 Feb 2014 16:08:38 GMT

Thank you, Gysbert and Johan, for your quick response. They are very helpful.