3 Replies Latest reply: Sep 5, 2017 5:03 PM by Rob Wunderlich RSS

    Load multiple files

    Francisco Cohen

      Hi,

      I need to insert a script that loads multiple files.

       

      What I have now is:

      LOAD

          "Submit date",

          "Lead ID",

          'Alex' & "Lead ID" as "ID"

      FROM [lib://Sales Funnel/Output\Alex Gerdts\Output_Alex.xlsx]

      (ooxml, embedded labels, table is Deliverables);

       

      LOAD

          "Submit date",

          "Lead ID",

          'Fred' & "Lead ID" as "ID"

      FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output_Fred.xlsx]

      (ooxml, embedded labels, table is Deliverables);

       

      I would like to have 2 arrays:

      1st with the file names: Output_Alex.xlsx, Output_Fred.xlsx

      2nd with the values to load: 'Alex','Fred'


      Then I would like to change the above code into something similar to this:

       

      for i=1 to Array1Lenght

      LOAD

          "Submit date",

          "Lead ID",

          Array2[i] & "Lead ID" as "ID"

      FROM Array1[i]

      (ooxml, embedded labels, table is Deliverables);

      next i

       

       

      Any ideas on how to do this?

      Thank you.

        • Re: Load multiple files
          Rob Wunderlich

          FOR EACH name IN 'Alex', 'Fred'

          LOAD

              "Submit date",

              "Lead ID",

              '$(name)' & "Lead ID" as "ID"

          FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output_$(name).xlsx]

          (ooxml, embedded labels, table is Deliverables);

          NEXT name

           

          -Rob

          http://masterssummit.com

          http://qlikviewcookbook.com

            • Re: Load multiple files
              Francisco Cohen

              Hi Rob,

              That worked nice!

              How do I do in order to have 2 different arrays, such as:

               

              FOR EACH name IN 'Alex', 'Fred'

              FOR EACH nickname IN 'A','F'

              LOAD

                  "Submit date",

                  "Lead ID",

                  '$(nickname)' & "Lead ID" as "ID"

              FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output_$(name).xlsx]

              (ooxml, embedded labels, table is Deliverables);

              NEXT name

              NEXT nickname

                • Re: Load multiple files
                  Rob Wunderlich

                  You can learn more about "For Each Next" in the help here:

                  For each..next ‒ Qlik Sense

                   

                  I understand what you want to do, and two FOR statements won't do it. The second is nested in the first, which means you will run the nickname twice for each name, for a total of 4 iterations:

                  Alex, A

                  Alex, F

                  Fred, A

                  Fred, F

                   

                  What you want is to run the loop only twice but have both the name and nickname available. You can combine the two as a single string and then separate using the SubField() function.

                   

                  FOR EACH combinedName IN 'Alex-A', 'Fred-F'

                  LET name = SubField('$(combinedName)', '-', 1);

                  LET nickname = SubField('$(combinedName)', '-', 2);

                  LOAD

                      "Submit date",

                      "Lead ID",

                      '$(nickname)' & "Lead ID" as "ID"

                  FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output$(name).xlsx]

                  (ooxml, embedded labels, table is Deliverables);

                  NEXT name

                   

                  -Rob

                  http://masterssummit.com

                  http://qlikviewcookbook.com