20 Replies Latest reply: Apr 4, 2016 7:58 AM by Ralf Becher RSS

    Loading attribute from a specific section in XML file

    Adnan Okko

      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>

           <organizer classCode="BATTERY" moodCode="EVN">

                     <code name="VC" codeSystem="2.16.840"/>

                     <component>

                               <observation classCode="OBS" moodCode="EVN">

                                    <value code="S006" />

                               </observation>

                     </component>

           </organizer>

           <organizer classCode="BATTERY" moodCode="EVN">

                     <code name="MB" codeSystem="2.16.840"/>

                     <component>

                               <observation classCode="OBS" moodCode="EVN">

                                    <value code="S003" />

                               </observation>

                     </component>

           </organizer>

      </entry>

       

      Please help. Thanks.

        • Re: Loading attribute from a specific section in XML file
          Ralf Becher

          Hi Adnan,

           

          this works like expected with the standard xml processing:

           

          // Start of [section.xml] LOAD statements
          organizer:
          LOAD classCode,
              moodCode,
              [component/observation/classCode] as [observation/classCode],
              [component/observation/moodCode] as [observation/moodCode],
              [component/observation/value/code] as code,
              [code/name] as name,
              [code/codeSystem] as codeSystem
          FROM [C:\Daten\section.xml] (XmlSimple, Table is [entry/organizer]);
          // End of [section.xml] LOAD statements
          

           

          - Ralf

            • Re: Loading attribute from a specific section in XML file
              Ralf Becher

              Or in Sense, just drag the xml file into the app and it will create a similar LOAD statement..

                • Re: Loading attribute from a specific section in XML file
                  Adnan Okko

                  Thank you Ralf.

                   

                  But what I really want to do is to load the "code" attribute only from the first organizer where name="VC", so I would get the value "S006" only. I don't know how to do this using script.

                    • Re: Loading attribute from a specific section in XML file
                      Ralf Becher

                      You can add a WHERE clause:

                       

                      [entry/organizer]:
                      LOAD 
                          [classCode],
                          [moodCode],
                          [component/observation/classCode],
                          [component/observation/moodCode],
                          [component/observation/value/code],
                          [code/name],
                          [code/codeSystem]
                      FROM [lib://Daten/section.xml]
                      (XmlSimple, table is [entry/organizer])
                      WHERE [code/name] = 'VC';
                      
                      

                       

                      - Ralf

                        • Re: Loading attribute from a specific section in XML file
                          Adnan Okko

                          Hi Ralf

                           

                          When I added the where statement, the LOAD worked fine on the previous section. But when I applied the same on the original XML file, it loaded empty fields. The XML is well formed. There is only one thing that I doubt; in the original XML file, each organizer has several components as above in the section. It's as follows:

                          <entry>

                               <organizer>

                                    <component>

                                    </component>

                                    <component>

                                    </component>

                               </organizer>

                               <organizer>

                                    <component>

                                    </component>

                                    <component>

                                    </component>

                               </organizer>

                          </entry>

                           

                          Could this be the issue?

                            • Re: Loading attribute from a specific section in XML file
                              Ralf Becher

                              There should be all components loaded for this organizer. To check this can you post a real sample here?

                                • Re: Loading attribute from a specific section in XML file
                                  Adnan Okko

                                  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").

                                    • Re: Loading attribute from a specific section in XML file
                                      Ralf Becher

                                      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

                                        • Re: Loading attribute from a specific section in XML file
                                          Adnan Okko

                                          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!?

                                            • Re: Loading attribute from a specific section in XML file
                                              Ralf Becher

                                              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

                                                • Re: Loading attribute from a specific section in XML file
                                                  Adnan Okko

                                                  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!

                                                  • Re: Loading attribute from a specific section in XML file
                                                    Ralf Becher

                                                    Then change the 2nd Where Clause to this:

                                                     

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

                                                     

                                                    - Ralf

                                                      • Re: Loading attribute from a specific section in XML file
                                                        Adnan Okko

                                                        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.

                                                          • Re: Loading attribute from a specific section in XML file
                                                            Ralf Becher

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

                                                              • Re: Loading attribute from a specific section in XML file
                                                                Adnan Okko

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

                                                                This is what I get.

                                                                  • Re: Loading attribute from a specific section in XML file
                                                                    Adnan Okko

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

                                                                      • Re: Loading attribute from a specific section in XML file
                                                                        Ralf Becher

                                                                        This has to do with the generated synthetic key. Please use this LOAD script and delete all unnecessary fields:

                                                                         

                                                                        [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],
                                                                            FileBaseName() & '_' & [%Key_entry_531F1B1B7C50DD70] as [%key]
                                                                        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 Match([observation/value/code], 'GQC004', 'GQC006');
                                                                        
                                                                        
                                          • Re: Loading attribute from a specific section in XML file
                                            Johan Lindell

                                            Or if you want just the first occurance nomatter code you can add "First":

                                             

                                            [entry/organizer]: 

                                            First 1

                                            LOAD  

                                                [classCode], 

                                                [moodCode], 

                                                [component/observation/classCode], 

                                                [component/observation/moodCode], 

                                                [component/observation/value/code], 

                                                [code/name], 

                                                [code/codeSystem] 

                                            FROM [lib://Daten/section.xml] 

                                            (XmlSimple, table is [entry/organizer])  ;