Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading XML data to Qlikview by using loops.

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="YSmiley TongueGFiles";

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

1 Solution

Accepted Solutions
Not applicable

Re: Loading XML data to Qlikview by using loops.


Okay

7 Replies
sudeepkm
Valued Contributor III

Re: Loading XML data to Qlikview by using loops.

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;

Not applicable

Re: Loading XML data to Qlikview by using loops.


Sundeep,

Thanks for your assistance, however the loop still does not work.

sudeepkm
Valued Contributor III

Re: Loading XML data to Qlikview by using loops.

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.

Not applicable

Re: Loading XML data to Qlikview by using loops.


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

sudeepkm
Valued Contributor III

Re: Loading XML data to Qlikview by using loops.

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.


Not applicable

Re: Loading XML data to Qlikview by using loops.


Okay

Not applicable

Re: Loading XML data to Qlikview by using loops.

Thanks for your help, everything works. 

Community Browser