For each doesn't work with text

Fitus9092 — Mon, 13 Nov 2023 11:21:09 GMT

Hello everyone,

I'm currently facing an issue with the "for each" cycle in my script. I'm working with a QVD file that contains numerous fields and records, where I load my data. Additionally, I have an Excel file with some codes (e.g., 1404, 1405, 1406, V5811, V5812).

The problem arises when I attempt to load these codes using the "for each" cycle, as I encounter errors for V5811 and V5812 but not for the others. Below is a snippet of the error and for my script:

Field not found error

Field 'V5811' not found

load

[N° SDO],

if( wildmatch(V5811,DiagnosiPrinc),1,0) as Conta1

Resident FLUSSO

These codes are in my qvd beacause i know it.

FLUSSO:
LOAD CodStruttura,
[N° SDO],
Cognome,
Nome,
Sesso,
left(DataNascita,2)&'/'&MID(DataNascita,3,2)&'/'&Right(DataNascita,4) as DataNascita,
ComuneNascita,
StatoCivicle,
ComuneResidenza,
Cittadinanza,
CodSanIndiv,
RegioneResidenza,
AzUSLResidenza,
RegimeRicovero,
left(DataRicovero,2)&'/'&MID(DataRicovero,3,2)&'/'&Right(DataRicovero,4) as DataRicovero,
MID(DataRicovero,3,2)&'/'&Right(DataRicovero,4) as MeseAnnoRicovero,
MID(DataRicovero,3,2) as MeseRicovero,
Right(DataRicovero,4) as AnnoRicovero,
UOammisione,
OnereDegenza,
Provenienza,
TipoRicovero,
TraumatismiIntoss,
TrasfInterno1Data,
text(TrasfInterno1) as TrasfInterno1,
TrasfInterno2Data,
text(TrasfInterno2) as TrasfInterno2,
TrasfInterno3Data,
text(TrasfInterno3) as TrasfInterno3,
TrasfInterno4Data,
text(TrasfInterno4) as TrasfInterno4,
UOdimissione,
left(DataDimissione,2)&'/'&MID(DataDimissione,3,2)&'/'&Right(DataDimissione,4) as DataDimissione,

MID(DataDimissione,3,2)&'/'&Right(DataDimissione,4) as MeseAnnoDimissione,
Right(DataDimissione,4) as AnnoDimissione,
ModalitDimissione,
RiscAutoptico,
MotivoRicovero,
NggPresenzaDH,
PesoNascita,
text(DiagnosiPrinc) as DiagnosiPrinc,
text(DiagnosiSec1) as DiagnosiSec1,
text(DiagnosiSec2) as DiagnosiSec2,
text(DiagnosiSec3) as DiagnosiSec3,
text(DiagnosiSec4) as DiagnosiSec4,
text(DiagnosiSec5) as DiagnosiSec5,
DiagnosiSec6,
DiagnosiSec7,
DiagnosiSec8,
DiagnosiSec9,
DiagnosiSec10,
DiagnosiSec11,
DiagnosiSec12,
DiagnosiSec13,
DiagnosiSec14,
DiagnosiSec15,
ProceduraPincData,
text(ProceduraPrinc) as ProceduraPrinc,
Procedura1Data,
text(Procedura1) as Procedura1,
Procedura2Data,
text(Procedura2) as Procedura2,
Procedura3Data,
text(Procedura3) as Procedura3,
Procedura4Data,
text(Procedura4) as Procedura4,
Procedura5Data,
text(Procedura5) as Procedura5,
Procedura6Data,
text(Procedura6) as Procedura6,
Procedura7Data,
text(Procedura7) as Procedura7,
Procedura8Data,
text(Procedura8) as Procedura8,
Procedura9Data,
text(Procedura9) as Procedura9,
Procedura10Data,
text(Procedura10) as Procedura10,
Procedura11Data,
Procedura11,
Procedura12Data,
Procedura12,
Procedura13Data,
Procedura13,
Procedura14Data,
Procedura14,
Procedura15Data,
Procedura15,
text(DRG) as DRG,
Codutilizzata,
Trimestre,
Vuoto,
[Importo totale],
Età,
MDC,
[Giorni degenza],
[tipo DRG],
[soglia DRG],
[peso relativo],
[data prenotazione],
protesi,
text([uo ric 2]) as [uo ric 2],
text([uo dim 2]) as [uo dim 2],
istruzione,
[classe priorità],
[Codice causa esterna],
[Campo vuoto]

FROM
[C:\Users\emanuele.ciaramitaro\Desktop\A 20 Anni\20082023.qvd]
(qvd);

lista_interventi:
LOAD
Codice as codice

FROM
[C:\Users\emanuele.ciaramitaro\Desktop\A 20 Anni\Lista diagnosi_2.xlsx]
(ooxml, embedded labels, table is Foglio1);

FOR Each a in FieldValueList('codice')
for each b in 'DiagnosiPrinc', 'DiagnosiSec1', 'DiagnosiSec2', 'DiagnosiSec3', 'DiagnosiSec4', 'DiagnosiSec5'
load

[N° SDO],

if( wildmatch($(a),$(b)),1,0) as Conta1
Resident FLUSSO;
next b
next a
Thanks.

Re: For each doesn't work with text

marcus_sommer — Mon, 13 Nov 2023 12:10:24 GMT

You are querying these values per $(a) within a wildmatch() which would only be a valid value if it's a number or a field-name but not by strings. You may try it with: '$(a)'

Re: For each doesn't work with text

Fitus9092 — Mon, 13 Nov 2023 12:25:23 GMT

Thanks, your solution work!

topic Re: For each doesn't work with text in QlikView

For each doesn't work with text

Re: For each doesn't work with text

Re: For each doesn't work with text