Loading XML data to Qlikview by using loops.

[#1]

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

[sudeepkm]

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;

[#3]

Sundeep,

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

[sudeepkm]

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.

[#5]

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]

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.

[#7 · Accepted solution]

Okay

[#8]

Thanks for your help, everything works.