Match fct

[silviacico]

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!

[sunny_talwar]

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

[silviacico]

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 " ' ' "

[sunny_talwar]

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

[silviacico]

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

[sunny_talwar]

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

[silviacico]

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

[#8]

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

Match(FieldName,'$(varibale)')

[silviacico]

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

[sunny_talwar · Accepted solution]

Try doing this:

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);

DROP Table StagingBU
NEXT i