Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I need help with loading data from xml to qlikview by using a looping function. My main objective is to extract data from multiple files within my computer to qlikview, then store it. Currently, my code is below, but it has not worked.
set Root="Y:PGFiles";
let q = 0;
FOR EACH file IN FILELIST('$(Root)\*.xml') ;
let q = q+1;
let ANALYSIS = "ANALYSIS";
let table_name="$(Analysis)" & "$(q)";
LOAD SURVEY_ID,
CLIENT_ID,
SERVICE,
RECDATE,
DISDATE,
%Key_PATIENTLEVELDATA_E1A59225A73AF151 // Key for this table: DATA_EXPORT/PATIENTLEVELDATA
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA]);
$("ANALYSIS"):
LOAD SERVICE,
VARNAME,
QUESTION_TEXT,
%Key_HEADER_8B8B72D6FCC98357 // Key to parent table: DATA_EXPORT/HEADER
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/HEADER/QUESTION_MAP/QUESTION]);
Concatenate $("ANALYSIS"):
LOAD
VARNAME ,
VALUE as AnalysisValue,
%Key_PATIENTLEVELDATA_E1A59225A73AF151
// Key to parent table: DATA_EXPORT/HEADER
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA/ANALYSIS/RESPONSE]);
Concatenate $("ANALYSIS"):
LOAD VARNAME,
VALUE as DemographicValues,
%Key_PATIENTLEVELDATA_E1A59225A73AF151
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA/DEMOGRAPHICS/RESPONSE]);
Concatenate $("ANALYSIS"):
LOAD VARNAME,
SENTIMENT as CommentSentiment,
VALUE as CommentValue,
%Key_PATIENTLEVELDATA_E1A59225A73AF151 // Key to parent table: DATA_EXPORT/PATIENTLEVELDATA
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA/COMMENTS/RESPONSE]);
you may need to use next; or next q;
for loop requires the "next" to iterate through.
For example:
for i = 1 to 3
// load statement
next i;
Sundeep,
Thanks for your assistance, however the loop still does not work.
Are you getting any error message?
Can you please check if you have the same key (e.g. %Key_PATIENTLEVELDATA_E1A59225A73AF151) in all your xml files.
I think the keys will be different in each xml file and in your script since you are using one key # so all other data may not be fetched. Pls see if you can share your script.
Sudeep,
Here is my script:
Root="Y:\PGFiles";
let q = 0;
FOR EACH file IN FILELIST('$(Root)\*.xml') ;
let q = q+1;
let ANALYSIS = "ANALYSIS";
let table_name="$(Analysis)" & "$(q)";
$("ANALYSIS"):
LOAD SURVEY_ID,
CLIENT_ID,
SERVICE,
RECDATE,
DISDATE,
%Key_PATIENTLEVELDATA_E1A59225A73AF151 // Key for this table: DATA_EXPORT/PATIENTLEVELDATA
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA]);
Concatenate $("ANALYSIS"):
LOAD
VARNAME ,
VALUE as AnalysisValue,
%Key_PATIENTLEVELDATA_E1A59225A73AF151
// Key to parent table: DATA_EXPORT/HEADER
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA/ANALYSIS/RESPONSE]);
Concatenate $("ANALYSIS"):
LOAD VARNAME,
VALUE as DemographicValues,
%Key_PATIENTLEVELDATA_E1A59225A73AF151
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA/DEMOGRAPHICS/RESPONSE]);
Concatenate $("ANALYSIS"):
LOAD VARNAME,
SENTIMENT as CommentSentiment,
VALUE as CommentValue,
%Key_PATIENTLEVELDATA_E1A59225A73AF151 // Key to parent table: DATA_EXPORT/PATIENTLEVELDATA
FROM $(FILE) (XmlSimple, Table is [DATA_EXPORT/PATIENTLEVELDATA/COMMENTS/RESPONSE]);
NEXT
The error message I get includes:
Cannot open file 'Y:\Development\' The system cannot find
the file specified and
Unknown statement
:
LOAD SURVEY_ID,
CLIENT_ID,
SERVICE,
RECDATE,
DISDATE,
%Key_PATIENTLEVELDATA_E1A59225A73AF151
FROM (XmlSimple, Table is
[DATA_EXPORT/PATIENTLEVELDATA])
Please use '$(ANALYSIS)': instead of $("ANALYSIS"):
The value of $("ANALYSIS") is null that's the first error you are getting.
I think it will be better if you use script debugger so that you can verify each steps of your script and can identify the issues.
Okay
Thanks for your help, everything works.