topic Re: Loading XML data to Qlikview by using loops. in QlikView

Loading XML data to Qlikview by using loops.

Mon, 14 Oct 2013 21:49:29 GMT

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

Re: Loading XML data to Qlikview by using loops.

sudeepkm — Tue, 15 Oct 2013 05:29:30 GMT

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;

Re: Loading XML data to Qlikview by using loops.

Tue, 15 Oct 2013 14:11:47 GMT

Sundeep,

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

Re: Loading XML data to Qlikview by using loops.

sudeepkm — Tue, 15 Oct 2013 15:08:44 GMT

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.

Re: Loading XML data to Qlikview by using loops.

Tue, 15 Oct 2013 16:07:05 GMT

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

Re: Loading XML data to Qlikview by using loops.

sudeepkm — Tue, 15 Oct 2013 16:26:02 GMT

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.

Re: Loading XML data to Qlikview by using loops.

Tue, 15 Oct 2013 16:36:21 GMT

Okay

Re: Loading XML data to Qlikview by using loops.

Tue, 15 Oct 2013 16:58:26 GMT

Thanks for your help, everything works.