topic Re: String search doubt in QlikView

String search doubt

Wed, 25 Nov 2020 16:16:04 GMT

Hi to all,

I have two variables that i've setted like this:

tmp:

NoConcatenate

Load concat(Distinct InactiveCAL,';') as CALToRemove

Resident InactiveCALs;

let CALToRemove = peek('CALToRemove',0,'tmp');

drop table tmp;

wltmp:

NoConcatenate

Load CAL as WLCAL

FROM data\whiteList.qvd(qvd);

let CALinWL = peek('WLCAL',0,'wltmp');

drop table wltmp;

The result is some like USERNAME1;USERNAME2;USERNAMEN for the 2 variables.

My problem now is that i need to compare this two varialbes to obtain a final value in another variable, for example:

Var1 = User1;User2;User3;User4 <-- This are the values to remove

Var2 = User1;User3 <-- This is my WhiteList

VarFin = User2;User4 <-- This value is the result of filter with Var2

Any idea how to filter this string?

Thanks in advance.

Re: String search doubt

sunny_talwar — Tue, 28 Jun 2016 15:26:00 GMT

May be create a third table like this:

TempTable:

NoConcatenate

Load SubField(CAL, ';') as Check

FROM data\whiteList.qvd(qvd);

VarFinTable:

LOAD Concat(VarFin, ';') as VarFin;

LOAD InactiveCAL as VarFin

Resident InactiveCALs

Where not Exists(Check, InactiveCAL);

LET VarFin = Peek('VarFin', 0, 'VarFinTable')'

DROP Tables TempTable, VarFinTable;

Re: String search doubt

Tue, 28 Jun 2016 15:54:08 GMT

Hi guys,

I've solved the problem using only the tables instead of strings of concatenate value in temporal tables:

Table 1 --> InactiveCAL

Table 2 --> WLCAL

let nRows = NoOfRows('Table 1');

LET removeCalList = Null();

FOR i = 0 to $(nRows)-1

LET CALInactive = peek('InactiveCAL', i, 'Table 2');

LET whiteListed = lookup('WLCAL', 'WLCAL', CALInactive, 'Table 1') ;

IF IsNull(whiteListed) then

IF IsNull(removeCalList) then

removeCalList = CALInactive;

ELSE

removeCalList = CALInactive & ';' & removeCalList;

ENDIF

Thanks for our time