topic Loading numerous weekly flat files in QlikView

Loading numerous weekly flat files

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

I have flat files that needed to be loaded in weekly that have the same fields in each one but the date is different at the end. What would be the best way to load these in to a script?

Each ends with the month and the date the report was generated, so like this: download_intl_invoice_report_feb28

How could I load each week in without having to add it to the script each time?

I would do the current week and day thing but reports might not be pulled on the day they are generated each time, so they load would need to pick any recently added weeks to the dashboard

Re: Loading numerous weekly flat files

marcus_sommer — Fri, 05 May 2017 11:14:17 GMT

You could use something like:

subfield(filebasename(), '_', 5) as FileDate

to extract feb28 from the filename. Another method might be to use the filetime with filetime().

- Marcus

Re: Loading numerous weekly flat files

khaycock — Fri, 05 May 2017 11:18:14 GMT

How would I incorporate this with the load?

Re: Loading numerous weekly flat files

marcus_sommer — Fri, 05 May 2017 11:32:15 GMT

You could it directly use within the load like:

table:

load *, subfield(filebasename(), '_', 5) as FileDate, filetime() as FileTime

from Source;

- Marcus

Re: Loading numerous weekly flat files

khaycock — Fri, 05 May 2017 12:53:40 GMT

Sorry if I sound stupid but I'm struggling to understand what this does? Why am I creating a field for the file? I want to make it so the source is flexible each load to what it is loading in.. So if i have a file that is for Feb21 and Feb28, it loads the both in with the same piece of script rather than me having to add both and concatenate.

Re: Loading numerous weekly flat files

RonaldDoes — Fri, 05 May 2017 12:59:15 GMT

Hi Kathryn,

Sounds like you just want to load all files, regardless of their names.

Tablename:

LOAD

{fields in your table}

FROM

download_intl_invoice_report_*;

The use of wildcards in filenames is permitted, so this will load download_intl_invoice_report_feb28 as well as download_intl_invoice_report_dec01

I hope this helps you.

With kind regards,

Ronald

Re: Loading numerous weekly flat files

marcus_sommer — Fri, 05 May 2017 13:01:31 GMT

Please provide the relevant script-part to your question.

- Marcus

Re: Loading numerous weekly flat files

khaycock — Fri, 05 May 2017 13:16:59 GMT

So this is what I have currently:

Left join (Oracle)

LOAD

'Non-Blue Box' AS Type,

[Account Number],

[Invoice Number],

[Invoice Date],

[Amount Due],

[Shipment Number],

[Collection Record],

[Reference No.1],

[Reference No.2],

[Reference No.3],

[Reference No.2] & '_' & [Reference No.1] AS KEY1,

([Collection Date]),

[Net Charges]

FROM

[..\..\Data\FlatFiles\ups_download_intl_invoice_report_Feb21.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Left join (Oracle)

LOAD

'Non-Blue Box' AS Type,

[Account Number],

[Invoice Number],

[Invoice Date],

[Amount Due],

[Shipment Number],

[Collection Record],

[Reference No.1],

[Reference No.2],

[Reference No.3],

[Reference No.2] & '_' & [Reference No.1] AS KEY1,

([Collection Date]),

[Net Charges]

FROM

[..\..\Data\FlatFiles\ups_download_intl_invoice_report_Feb28.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

But ideally I want to be able to load them all at once, with * or a variable or something that means I haven't got to add each week individually

Re: Loading numerous weekly flat files

marcus_sommer — Fri, 05 May 2017 13:23:02 GMT

You could just use wildcards within the filename like:

FROM [..\..\Data\FlatFiles\*.csv]

FROM [..\..\Data\FlatFiles\ups_download_intl_*_report_???*.csv]

- Marcus

Re: Loading numerous weekly flat files

khaycock — Mon, 08 May 2017 09:56:20 GMT

Great thanks. I have a similar situation where I have data tables for each year and i only want to load from 2015 upwards. For example, the tables are called TABLE2015, TABLE2016 and TABLE2017. Can I use wildcards for these? There are also tables that have 2014 and 2013 data so I can't just do TABLE*

Re: Loading numerous weekly flat files

prieper — Mon, 08 May 2017 10:07:08 GMT

Then you may use FILELIST():

FOR EACH sFile IN FILELIST(MyFileSpecification)

IF WILDMATCH ('$(sFile)', '*2015*', '*2016*') THEN

LOAD ...... FROM $(sFile);

END IF

NEXT sFile

Peter

edit: Wildcards added

Re: Loading numerous weekly flat files

khaycock — Tue, 09 May 2017 10:00:05 GMT

Where do i declare the location of the qvd?

Re: Loading numerous weekly flat files

prieper — Tue, 09 May 2017 12:43:55 GMT

You may do it in the FILELIST, e.g.

FOR EACH sFile IN FILELIST('C:\TEMP\MyQVD*.QVD')

....

Else you may use a variable (inputbox or via script):

LET sFilePattern = 'C:\TEMP\MyQVD*.QVD';

....

FOR EACH sFile IN FILELIST('$(sFilePattern)')

....

Rgds Peter

Re: Loading numerous weekly flat files

khaycock — Tue, 09 May 2017 14:58:21 GMT

Amazing thank you!!!

Re: Loading numerous weekly flat files

ttollin11 — Fri, 21 Sep 2018 14:02:07 GMT

Marcus,

Sorry for the thread resurrection. Is there a way to get these concatenated when loading this way?

Re: Loading numerous weekly flat files

marcus_sommer — Fri, 21 Sep 2018 14:15:08 GMT

As far as the table-structure is the same all files will be automatically concatenated. To ensure this you need to specify all needed fields instead of using a wildcard like: load * from ... so that no new field could disrupt your load-logic. If new fields should be included or there are some missing you need an explicit concatenate(Table) statement - created by a conditionally statement or even fix to an empty table like:

dummy: load 'dummy' as dummy autogenerate 0;

concatenate(dummy)

load YourLoadStuff ...

drop fields dummy;

- Marcus