Load a variable file name

[racer25]

Hi 

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

[madelonjansen]

Hi 🙂

Try it with square brackets around your vFile:

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

[racer25]

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

[madelonjansen]

Hmm...

Can you share the error you're getting?

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

[marksouzacosta]

Hi @racer25,

Can you please try this?

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

[racer25]

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

[marksouzacosta]

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.

[racer25]

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

[marksouzacosta]

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.