Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
khaycock
Contributor

Loading numerous weekly flat files

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

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Loading numerous weekly flat files

You could just use wildcards within the filename like:

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

or

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

- Marcus

View solution in original post

15 Replies
MVP & Luminary
MVP & Luminary

Re: Loading numerous weekly flat files

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

khaycock
Contributor

Re: Loading numerous weekly flat files

How would I incorporate this with the load?

MVP & Luminary
MVP & Luminary

Re: Loading numerous weekly flat files

You could it directly use within the load like:

table:

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

from Source;

- Marcus

khaycock
Contributor

Re: Loading numerous weekly flat files

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.

Partner
Partner

Re: Loading numerous weekly flat files

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

MVP & Luminary
MVP & Luminary

Re: Loading numerous weekly flat files

Please provide the relevant script-part to your question.

- Marcus

khaycock
Contributor

Re: Loading numerous weekly flat files

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

MVP & Luminary
MVP & Luminary

Re: Loading numerous weekly flat files

You could just use wildcards within the filename like:

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

or

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

- Marcus

View solution in original post

khaycock
Contributor

Re: Loading numerous weekly flat files

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*