Loop files and variables

[#1]

Hi there,

I've seen some some questions about this problem but they don't seem to suit my particular problem. I have the following issue. I have several data sources from separate business units. They all contain the same data fields and they need to be loaded in the same table but all need a qualifier within the table to retrieve the original source. So I have for two business units:

FUELACTIVITY.QVD

GASACTIVITY.QVD

with qualifiers

'Fuel'

'Gas'

The script for FUEL looks as shown below

T_ORDERS:
LOAD #OrderID,
     ArgosCompany,
     OrderDate,
     CustomerLocation,
     SUM(OrderQuantity) AS OrderQuantity
GROUP BY
  #OrderID, ArgosCompany,OrderDate,CustomerLocation;
LOAD SiteId & DATE(ShiftDate,'DDMMYYYY') AS #OrderID,
           'Fuel' AS ArgosCompany,
     DATE(ShiftDate, 'DD-MMM-YYYY') AS OrderDate,
     SiteId AS CustomerLocation,
     IF(Activity = 'Load', LoadedQty, DeliveredQty) AS OrderQuantity
FROM
FUELACTIVITY.QVD
(qvd)
WHERE(MATCH(Source,'DP','DC') AND ((Activity = 'Load' OR Activity = 'Delivery') AND (LoadedQty > 0 OR DeliveredQty > 0)));

How can I incorporate the looping over several files with qualifiers? If this can be done using an Inline load that would work perfect since there is only a limited number of business units but using an xls could also work. Thanks in advance!

Cheers Donald

[#2]

Hi,

Since you are using Flag column ArgosCompany, don't use qualifier. Go for concatenate.

Apart from this there are filelist() available.

HTH

Reg,

Shubhu

[#3]

Hi Shubhu,

I just figured it out. And indeed the Qualifier is gone now. Thanks for the help!

[#4]

Just one more question. Right now I'm using an inline statement with LET statements and variables for the Filenames. How would I populate a filelist?

[gandalfgray]

Search for filelist in the Help

there is an example in the for each..next

The easiest is if you can place your infiles in the same directory, and have some wildcard-string that will match all your filenames.