Solved: Re: Loading attribute from a specific section in X... - Page 2 - Qlik Community

Report Inappropriate Content · ‎2016-03-30

Hi

I need to load the value of attribute "code" from the first "organizer" according to the attribute "name" as a reference;

For example: load the value of attribute "code" from the section where attribute name="VC". ( I should get S006).

Here is the XML code:

<entry>

</observation>

</component>

</organizer>

</observation>

</component>

</organizer>

</entry>

Please help. Thanks.

Report Inappropriate Content · ‎2016-04-01

Hello Ralf

In the attached XML file you can find two "entry" nodes containing the organizers. I want to get the value of attribute [value/code] from the from the organizer where [code/code]='MMFVC'; (I should get "GQC006"). And in the same way I can get [value/code] from the second organizer where [code/code]='MMFVCMB'; (Here I should get "GQC004").

rbecher · ‎2016-04-02

Somthing like this:

[publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry]:
LOAD
[typeCode],
    [observationMedia/ID],
    [observationMedia/classCode],
    [observationMedia/moodCode],
    [observationMedia/value],
    [observationMedia/value/mediaType],
    [observationMedia/value/representation],
    [observationMedia/templateId/extension],
    [observationMedia/templateId/root],
    [organizer/classCode],
    [organizer/moodCode],
    [organizer/statusCode/code],
    [organizer/code/code],
    [organizer/code/codeSystem],
    [organizer/code/displayName],
    [observation/classCode],
    [observation/moodCode],
    [observation/value/unit],
    [observation/value/xsi:type],
    [observation/value/value],
    [observation/value/code],
    [observation/value/codeSystem],
    [observation/value/codeSystemName],
    [observation/value/displayName],
    [observation/statusCode/code],
    [observation/code/code],
    [observation/code/codeSystem],
    [observation/code/displayName],
    [observation/templateId/extension],
    [observation/templateId/root],
    [%Key_section_8DE2BFA8AE16711D],
    [%Key_entry_531F1B1B7C50DD70]
 FROM [lib://Daten/Section1.xml]
(XmlSimple, table is [publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry])
Where Match([organizer/code/code], 'MMFVC', 'MMFVCMB');

[publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry/organizer/component]:
LOAD 
[organizer/classCode],
    [organizer/moodCode],
    [organizer/statusCode/code],
    [sequenceNumber/value],
    [observation/classCode],
    [observation/moodCode],
    [observation/value/unit],
    [observation/value/value],
    [observation/value/xsi:type],
    [observation/value/code],
    [observation/value/displayName],
    [observation/value/codeSystem],
    [observation/value/digits],
    [observation/value/scale/unit],
    [observation/value/scale/value],
    [observation/value/origin/unit],
    [observation/value/origin/value],
    [observation/statusCode/code],
    [observation/code/code],
    [observation/code/codeSystem],
    [observation/code/displayName],
    [observation/templateId/extension],
    [observation/templateId/root],
    [%Key_entry_531F1B1B7C50DD70],
    [%Key_component_FF3ABE3584473A7D]
 FROM [lib://Daten/Section1.xml]
(XmlSimple, table is [publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry/organizer/component])
Where Exists([%Key_entry_531F1B1B7C50DD70]) AND [observation/code/code] = 'GQC';

- Ralf

Astrato.io Head of R&D

Report Inappropriate Content · ‎2016-04-03

Thanks a lot Ralf

The code works well for the file "Section1".

I have several XML files (Section1, Section2,......, Section70) in a folder, and I am using the above code to load data (GQC006, GQC004) from all of them using the directory as follows:

FROM [lib://..../*.xml]

The first LOAD fetches data from 70 files, but the second LOAD loads the similar values as one!; if I have "GQC006" in all the files it only loads one, no redundancy!?

rbecher · ‎2016-04-03

The XML keys are like autonumbers and unique per file only. Hence you have to create your own global unique key. You can easily do this by adding the file name:

[publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry]:
LOAD 
[typeCode],
    [observationMedia/ID],
    [observationMedia/classCode],
    [observationMedia/moodCode],
    [observationMedia/value],
    [observationMedia/value/mediaType],
    [observationMedia/value/representation],
    [observationMedia/templateId/extension],
    [observationMedia/templateId/root],
    [organizer/classCode],
    [organizer/moodCode],
    [organizer/statusCode/code],
    [organizer/code/code],
    [organizer/code/codeSystem],
    [organizer/code/displayName],
    [observation/classCode],
    [observation/moodCode],
    [observation/value/unit],
    [observation/value/xsi:type],
    [observation/value/value],
    [observation/value/code],
    [observation/value/codeSystem],
    [observation/value/codeSystemName],
    [observation/value/displayName],
    [observation/statusCode/code],
    [observation/code/code],
    [observation/code/codeSystem],
    [observation/code/displayName],
    [observation/templateId/extension],
    [observation/templateId/root],
    [%Key_section_8DE2BFA8AE16711D],
    [%Key_entry_531F1B1B7C50DD70],
    FileBaseName() & '_' & [%Key_entry_531F1B1B7C50DD70] as [%key]
 FROM [lib://Daten/Section*.xml]
(XmlSimple, table is [publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry])
Where Match([organizer/code/code], 'MMFVC', 'MMFVCMB');

[publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry/organizer/component]:
LOAD 
[organizer/classCode],
    [organizer/moodCode],
    [organizer/statusCode/code],
    [sequenceNumber/value],
    [observation/classCode],
    [observation/moodCode],
    [observation/value/unit],
    [observation/value/value],
    [observation/value/xsi:type],
    [observation/value/code],
    [observation/value/displayName],
    [observation/value/codeSystem],
    [observation/value/digits],
    [observation/value/scale/unit],
    [observation/value/scale/value],
    [observation/value/origin/unit],
    [observation/value/origin/value],
    [observation/statusCode/code],
    [observation/code/code],
    [observation/code/codeSystem],
    [observation/code/displayName],
    [observation/templateId/extension],
    [observation/templateId/root],
    [%Key_entry_531F1B1B7C50DD70],
    [%Key_component_FF3ABE3584473A7D]
 FROM [lib://Daten/Section*.xml]
(XmlSimple, table is [publicarEspirometries/ClinicalDocument/component/structuredBody/component/section/entry/organizer/component])
Where Exists(%key, FileBaseName() & '_' & [%Key_entry_531F1B1B7C50DD70]) 
AND [observation/code/code] = 'GQC';

- Ralf

Astrato.io Head of R&D

Report Inappropriate Content · ‎2016-04-03

Thanks Ralf

I applied the global key but I still don't get all the redundant values. the value "GQC006" is the same in all files. When I changed it in one file, the script loaded the new value!

rbecher · ‎2016-04-04

Then change the 2nd Where Clause to this:

Where Exists(%key, FileBaseName() & '_' & [%Key_entry_531F1B1B7C50DD70]) 
AND Match([observation/value/code], 'GQC004', 'GQC006');

- Ralf

Astrato.io Head of R&D

Report Inappropriate Content · ‎2016-04-04

Those values are temporary, they are not references. I want to load redundant real values.

The attached photo shows what the script loads from all the 70 files, whereas I want a list from all the files.

rbecher · ‎2016-04-04

Yes, Qlik Sense shows the values unique in a "listbox". What if you show a table with the two fields %key and [observations/value/code] ?

Astrato.io Head of R&D

Report Inappropriate Content · ‎2016-04-04

There is no prefix for the %key as in the first load!

This is what I get.

Report Inappropriate Content · ‎2016-04-04

The %key from the first load is as in the attached photo.

Loading attribute from a specific section in XML file