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 & "Lead ID" as "ID"

FROM Array1

(ooxml, embedded labels, table is Deliverables);

next i

Any ideas on how to do this?

Thank you.

[rwunderlich]

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

[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

[rwunderlich · Accepted solution]

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