topic Loop files and variables in QlikView

Loop files and variables

Tue, 30 Aug 2011 09:17:22 GMT

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

Loop files and variables

Tue, 30 Aug 2011 11:05:36 GMT

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

Loop files and variables

Tue, 30 Aug 2011 11:33:40 GMT

Hi Shubhu,

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

Loop files and variables

Tue, 30 Aug 2011 11:34:36 GMT

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?

Loop files and variables

gandalfgray — Tue, 30 Aug 2011 12:19:48 GMT

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.