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.
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)'
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)'
