topic Re: Match fct in QlikView

Match fct

silviacico — Tue, 20 Oct 2015 14:57:23 GMT

Hi all,

I have a problem with my QlikView project: I have to do a match function between a field and a variable, but it does not seem to work fine (in this way it gives as error "Field not found"

FOR i = 0 to $(noRows) - 1
LET BU = peek('BU_file', $(i));

NoConcatenate
StagingBU:
load *,
Resident Staging
where match(BU_file, $(BU));

next i

I have also tried to do:

FOR i = 0 to $(noRows) - 1

LET BU = FieldValue(peek('BU_file', $(i)),$(i));

load *,
'$(BU)' as Field1
Resident Staging
where match(BU_file, Field1);

next i ;

but is still not working.

Thanks!

Re: Match fct

sunny_talwar — Tue, 20 Oct 2015 15:23:57 GMT

Give this a try:

FOR i = 0 to $(noRows) - 1
LET BU = Chr(39) & Peek('BU_file', $(i)) & Chr(39);

NoConcatenate
StagingBU:
load *,
Resident Staging
where match(BU_file, $(BU));

NEXT i

Re: Match fct

silviacico — Tue, 20 Oct 2015 15:33:12 GMT

Thank you for your answer,

with your script the loop takes a correct value with the first play (i=0) but then BU takes value " ' ' "

Re: Match fct

sunny_talwar — Tue, 20 Oct 2015 18:01:23 GMT

Try this:

Temp:

LOAD Concat(BU_file, '|') as List

Resident Staging;

LET vList = Chr(39) & Peek('List') & Chr(39);

DROP Table Temp;

FOR i = 0 to $(noRows) - 1
LET BU = Chr(39) & SubField($(vList), '|', $(i)) & Chr(39);

NoConcatenate
StagingBU:
load *,
Resident Staging
where match(BU_file, $(BU));

NEXT i

Re: Match fct

silviacico — Wed, 21 Oct 2015 14:46:11 GMT

I need again your help, if I try to do this, it creates all the different QVD with correct names but it takes only records where BU_file = APP (instead it gives all the BUs names to QVDs)

Temp:

LOAD Concat(BU_file, '|') as List
Resident Stag_BU;

LET vList = Chr(39) & Peek('List') & Chr(39);
DROP Table Temp;

FOR i = 1 to $(noRows)
LET BU = Chr(39) & SubField($(vList), '|', $(i)) & Chr(39);

NoConcatenate
StagingBU:
load *
Resident Staging
where match(BU_file, $(BU));

store StagingBU into $(path_qvd)Staging_$(BU)_$(ANNO)_$(MESE).qvd (QVD);

NEXT i

Re: Match fct

sunny_talwar — Wed, 21 Oct 2015 15:47:46 GMT

Try adding Distinct to the Concat function:

Temp:
LOAD Concat(DISTINCT BU_file, '|') as List
Resident Stag_BU;

LET vList = Chr(39) & Peek('List') & Chr(39);
DROP Table Temp;

FOR i = 1 to $(noRows)
LET BU = Chr(39) & SubField($(vList), '|', $(i)) & Chr(39);

NoConcatenate
StagingBU:
load *
Resident Staging
where match(BU_file, $(BU));

store StagingBU into $(path_qvd)Staging_$(BU)_$(ANNO)_$(MESE).qvd (QVD);

NEXT i

Re: Match fct

silviacico — Wed, 21 Oct 2015 15:56:02 GMT

There's the same problem, it only takes data with BU_file= APP, this means that the code not working is:

where match(BU_file, $(BU));

I also tried with BU_file = $(BU) but still not working

Re: Match fct

Wed, 21 Oct 2015 16:04:51 GMT

Match is a string function and so the second variable in Match function also need string. So try like below:

Match(FieldName,'$(varibale)')

Re: Match fct

silviacico — Wed, 21 Oct 2015 16:08:38 GMT

Hi, in particular the code is working fine with the first value of the variabile BU (BU=APP) but then it's like the loop of loading and creating new QVDs is going on but not the where condition that is still "freezed" at BU=APP,

thanks

Re: Match fct

sunny_talwar — Wed, 21 Oct 2015 16:17:47 GMT

Try doing this:

DROP Table StagingBU
NEXT i

Re: Match fct

silviacico — Wed, 21 Oct 2015 16:19:59 GMT

Thank you so much!

Re: Match fct

sunny_talwar — Wed, 21 Oct 2015 16:39:31 GMT

Not a problem

I am glad I was able to help.

Best,

Sunny

Re: Match fct

silviacico — Fri, 06 Nov 2015 11:53:37 GMT

Hi, I found another problem with this script:

it seems not to work with Qlik Sense, the variables take the values but the "store" does not work with variables.

It only creates a QVD "Staging_$(BU)_$(ANNO)_$(MESE).qvd"

Re: Match fct

silviacico — Fri, 06 Nov 2015 13:14:05 GMT

Thanks dathu for the answer, here the problem is only using Sense, because the same script works perfectly with QlikView

Re: Match fct

sunny_talwar — Fri, 06 Nov 2015 16:01:08 GMT

Not entirely sure with how Qlik Sense work. There might be another way of using variables in Qlik Sense and don't really have the expertise to help you with it.