topic Re: Load a variable file name in QlikView

Load a variable file name

racer25 — Fri, 13 Sep 2024 09:50:24 GMT

I am trying (in vain) to get a dynamic file name loaded.

The file is located at

C:\Temp\SEPA\Pending Upload

I have a variable vFolder C:\Temp\SEPA

Mu script which worked until we moved servers...

For Each vFolder in DirList('Pending Upload*')

FOLDER:

Load

'$(vFolder)' As Folder

Autogenerate(1);

For Each vFile in FileList('$(vFolder)\*.xml')

FILE:

Load

'$(vFile)' As File

Autogenerate(1);

trace $(vFile);

Templates:

First 1

PmtInf:

LOAD PmtInfId,

PmtMtd,

NbOfTxs,

CtrlSum,

ReqdExctnDt,

PmtInfId & CtrlSum as JNL_KEY,

[CdtTrfTxInf/CdtrAcct/Id/IBAN] as [CdtrAcct/Id/IBAN],

[CdtTrfTxInf/Cdtr/Nm] as [Cdtr/Nm],

[CdtTrfTxInf/Amt/InstdAmt] as InstdAmt,

[CdtTrfTxInf/Amt/InstdAmt/Ccy] as Ccy,

[CdtTrfTxInf/PmtId/EndToEndId] as EndToEndId,

[DbtrAgt/FinInstnId/Othr/Id] as [FinInstnId/Othr/Id],

[DbtrAcct/Id/IBAN],

[Dbtr/Nm],

%Key_Document_5537B40932C780D8, // Key to parent table: Document

'$(vFile)' as Key

FROM $(vFile) (XmlSimple, Table is [Document/CstmrCdtTrfInitn/PmtInf]);

The load appears to do nothing.so obviously I have something wrong. Any thoughts or suggestions would be appreciated.

Thanks

Rob

Re: Load a variable file name

madelonjansen — Fri, 13 Sep 2024 10:48:53 GMT

Hi 🙂

Try it with square brackets around your vFile:

FROM [$(vFile)] (XmlSim....

Re: Load a variable file name

racer25 — Fri, 13 Sep 2024 11:18:40 GMT

Tried that and no joy. but thanks for taking a look

Re: Load a variable file name

madelonjansen — Fri, 13 Sep 2024 11:34:08 GMT

Hmm...

Can you share the error you're getting?

And what is the output of trace $(vFile); ?

Re: Load a variable file name

marksouzacosta — Fri, 13 Sep 2024 13:18:47 GMT

Hi @racer25,

Can you please try this?

For Each vFolder in DirList('C:\Temp\SEPA\Pending Upload\*')

Re: Load a variable file name

racer25 — Fri, 13 Sep 2024 14:23:14 GMT

Thanks for the replies

No joy on amending the file path to

For Each vFolder in DirList('C:\Temp\SEPA\Pending Upload\*')

When I "Step" through the script it gets to that line and immediately goes to the end without error..

Re: Load a variable file name

marksouzacosta — Fri, 13 Sep 2024 14:29:17 GMT

Hmmm. You can try this:

In the very same QlikView App, temporarily load one of those XML files using Qlik Wizard and see the file path that QlikView is using and compare against the folder you are using in your DirList. We are probably missing something there.

Re: Load a variable file name

racer25 — Fri, 13 Sep 2024 14:38:18 GMT

Just a thought as I read another post am I over complicating things. There will only ever be one file in that folder and I was looking at this option

Solved: Load multiple files - Qlik Community - 87076

My XML load gave me this which does suggest we have the file path right.

// Start of [PAIN001AON20240827092220.xml] LOAD statements
PmtInf:
LOAD PmtInfId,
PmtMtd,
NbOfTxs as [PmtInf/NbOfTxs],
CtrlSum as [PmtInf/CtrlSum],
ReqdExctnDt,
[CdtTrfTxInf/CdtrAcct/Id/IBAN] as [CdtrAcct/Id/IBAN],
[CdtTrfTxInf/Cdtr/Nm] as [Cdtr/Nm],
[CdtTrfTxInf/Amt/InstdAmt] as InstdAmt,
[CdtTrfTxInf/Amt/InstdAmt/Ccy] as Ccy,
[CdtTrfTxInf/PmtId/EndToEndId] as EndToEndId,
[DbtrAgt/FinInstnId/Othr/Id] as [FinInstnId/Othr/Id],
[DbtrAcct/Id/IBAN],
[Dbtr/Nm],
%Key_Document_5537B40932C780D8 // Key to parent table: Document
FROM [C:\Temp\SEPA\Pending Upload\PAIN001AON20240827092220.xml] (XmlSimple, Table is [Document/CstmrCdtTrfInitn/PmtInf]);

Document:
LOAD [CstmrCdtTrfInitn/GrpHdr/MsgId] as MsgId,
[CstmrCdtTrfInitn/GrpHdr/CreDtTm] as CreDtTm,
[CstmrCdtTrfInitn/GrpHdr/NbOfTxs] as [GrpHdr/NbOfTxs],
[CstmrCdtTrfInitn/GrpHdr/CtrlSum] as [GrpHdr/CtrlSum],
[CstmrCdtTrfInitn/GrpHdr/InitgPty/Id/PrvtId/Othr/Id] as [PrvtId/Othr/Id],
%Key_Document_5537B40932C780D8 // Key for this table: Document
FROM [C:\Temp\SEPA\Pending Upload\PAIN001AON20240827092220.xml] (XmlSimple, Table is [Document]);
// End of [PAIN001AON20240827092220.xml] LOAD statements

Re: Load a variable file name

marksouzacosta — Fri, 13 Sep 2024 17:03:42 GMT

I see. Yes, if that is the case, you don't need the DirList and the other things.

Regardless that, to make your code work with the DirList I guess you need this:

For Each vFolder in DirList('C:\Temp\SEPA\*')

So, this should find the Pending Upload directory that will have the XML file in it.