Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sakshikaul
Creator II
Creator II

How to load multiple files n qlikview from FTP (source)?

Hi

I want to load multiple files of material master in qlikview. and source is FTP. .here in below image you can see there are multiple files ie full load and some changed records are also kept in the same folder. so how to hamdle the same ?Untitled.png

30 Replies
marcus_sommer

Probably it will be enough to use:

set ErrorMode=0;
for i = 1 to 14
     for ii = 1 to 60
          vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYYMMDD') & '_0000' & num($(ii), '00') & '.txt';
          ...
     next
next
set ErrorMode=1;

Depending on your real requirements it might be necessary to implement one or several statements like:

if ScriptError=8 then
   ... this
else
   ...that
end if

within your ERRORMODE statements. Just look in the help for the various error-variables and error-codes.

 - Marcus

sakshikaul
Creator II
Creator II
Author

Hi 

I hope this is  the final solution to implement (load multiple files in qlikview  from FTP (source)). I want you to check once  as I am still doubtful that  following code will be used for following :-

It will only load  the current file(created on basis of current date) and ignoring the rest( if others are present on FTP it should be ignored) . When the new file will be created tomorrow with following file name ie MAT_MAS/MAT_MAS_20190105_000012.txt so, only current file ie MAT_MAS/MAT_MAS_20190105_000012.txt will be loaded and not the earlier one........

 is the following code correct?

set FTPServer = ftp://userid:userpass@10.47.77.207;
set Path = /IFR/IAP/QLK/MAT_MAS/;
set File_Prefix = MAT_MAS_;
set ErrorMode=2;
for i = 1 to 14
for ii = 1 to 60
vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYYMMDD') & '_0000' & num($(ii), '00') & '.txt';
Material_Master_full:
LOAD MATERIAL,
COLOR,
SIZE,
[Size Group],
Description,
COMPOSITION,
BRANDCD,
BRAND,
SEASON,
SEASONDESC,
THEME,
DELYCODE,
GENDER,
ProductHierarchy,
MaterialGroup,
[Core/Fashion],
FIT,
PATTERN,
[Key/NonKey],
RANGE,
ORIGIN,
[Top/Bottom],
SLEEVE,
NECK,
WEATHER,
OCCASSION,
CustomDesc,
MaterialGrp4,
MGRP4DESC,
EAN,
ALTEAN,
PurchaseGroup,
PurchaseGroupDesc,
CATEGORY,
SUBCAT,
BASECOMP,
MaterialType,
ValuationClass,
COST,
MRP,
ChangeDt
FROM
[ftp://userID:userpass@10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_20190104_000012.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);


next
next

marcus_sommer

No, it won't work in this way because there is no logic to skip already loaded files. For this you will need to store the already loaded files within a variable or a table and then comparing it against the generic filepath from the loop, maybe with something like this:

files: load * from files.qvd (qvd);

for ...
   for ...
      let vFileName ....
          if fieldindex('YourFiles', '$(vFileName)') = 0 then
              load ...
                 if ScriptError = 0 then
                     files: load '$(vFileName)' as YourFiles autogenerate 1;
                 end if
           end if
     next
next

store files into files.qvd (qvd);

Beside this you used 2 as ERRORMODE which will break the execution by any failure and you need to use the variable from the loop for the filename in the load.

- Marcus

sakshikaul
Creator II
Creator II
Author

Hi

From where I have to upload these files???? here in given scenario my all files are preset on FtP so how it is possible to convert these files on qvd????

Please help me in this rest I will manage my self

files: load * from files.qvd (qvd);

where these files will be stored?

Do I need to convert it into qvd  and store  on ftp only ?

Please check the rest of the code as follows:-

Files:
load * from files.qvd (qvd);
set FTPServer = ftp://userid:userpass@10.47.77.207;
set Path = /IFR/IAP/QLK/MAT_MAS/;
set File_Prefix = MAT_MAS_;
for i = 1 to 14
for ii = 1 to 60
let vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYYMMDD') & '_0000' & num($(ii), '00') & '.txt';


if fieldindex('YourFiles', '$(vFileName)') = 0 then
load * from files.qvd (qvd);
if ScriptError = 0 then
files: load '$(vFileName)' as YourFiles autogenerate 1;
end if
end if
next
next

store files into files.qvd (qvd);

 

 

marcus_sommer

I suggest to store the qvd's on a local storage and not on the ftp. IMO a ftp-server is (conditional) suitable as a transfer-storage but surely not to keep data which are productive used - and this means the qvd's as well as the txt-files.

- Marcus

sakshikaul
Creator II
Creator II
Author

Hi 

It is not possible for me to take the files on local storage but please  help is it possible to create Qvd's and load qvd's frpm Ftp ???

If Yes , How ??

I need solution to this problrm only rest will be manged.

 

marcus_sommer

Why is it not possible? It would really simplify the task and usally it's a better strategy to solve simple tasks within a well-defined process instead of creating really sophisticated logics in bad ones.

Beside this I think that you have already everything what you need. You just need to implement it step by step - at first the outer loop and tracing the filepath-variable for the dates, then the inner one with the times and then the if-loop to check if you could skip already loaded files and handle the errors and then the next step ... It's not very difficult with the already provided code - for each step you might need 10 times to find and correct the errors - and the important thing you will understand the logics behind it - if you would get a plug'n play solution you would never learn to solve similar challenges.

- Marcus

sakshikaul
Creator II
Creator II
Author

Hi,

I have implemented the same by using bat file. I have stored all Files which were created on FTP on local network and then stored in qvd like as follows:-

Order_Data:
LOAD distinct
Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM
[D:\Qlik_OrderSheets\OrderSheets\QLIK*]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
store Order_Data into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Order_Data.qvd;
drop table Order_Data;

Now I want to just confirm is there need to handle incremental load ?

since now my scenario has been changed Now all the changed records will be added or inserted in full load only and that file will be created on daily basis(and it will not include duplicate records)........

For Eg :-

I have three files (based on seasons) created on FTP (see image below) and now  changed records(incremental load ) will be updated,deleted or added in existing file only but that file be created on daily basis 

paint.png

Now if I want that old files should not be loaded and the file which is created on daily basis should only be loaded so following code will  work?If this code will work then please confirm are these two files (marked in red ) same ?

Order_Data:
LOAD *
FROM
[..\QVD\extract_qvd\Order_Data.qvd]
(qvd);

set FTPServer = ftp:/userid:userpass@10.47.77.207;
set Path = /IFR/IAP/QLK/ORDER_DATA/;
set File_Prefix = QLIK;
for i = 1 to 14
for ii = 1 to 60
let vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYYMMDD') & '_0000' & num($(ii), '00') & '.txt';


if fieldindex('YourFiles', '$(vFileName)') = 0 then
Order_Data:
LOAD *
FROM
[..\QVD\extract_qvd\Order_Data.qvd]
(qvd);
if ScriptError = 0 then
Order_Data:
load '$(vFileName)' as YourFiles autogenerate 1;
end if
end if
next
next

marcus_sommer

If you transfers the files to a local storage then you don't need the both for-loops else a for-each filelist() loop would be suitable, within the help by for each and here are Loops-in-the-Script.

In this loop you could check like in the above comments the date/time from the filename and other things before you load it. In which way you could apply the incremental loading depends on your requirements which types of updates are needed (append, change, delete) and which fields/data are available to identify the appropriate records - usually this are dates, unique ID's and/or change-timestamps. To all of them are here various examples available: Advanced-topics-for-creating-a-qlik-datamodel.

- Marcus

sakshikaul
Creator II
Creator II
Author

but what if I will use following to load only the current files and not to load old one 

but using this code following variable is not functioning (marked in red color)

LET vToday = date(today(), 'YYYYMMDD');
Order_Data:
LOAD distinct
Plant,
OrderNo,
OrderDate,
OrderType,
DistChannel,
Division,
NCODE,
Season,
MatType,
Style,
Color,
Size,
OrderQty,
OrderValue,
ConfirmedQty,
ConfirmedValue,
MRP
FROM $( vToday)[D:\Qlik_OrderSheets\OrderSheets\QLIK*]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
store Order_Data into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Order_Data.qvd;
drop table Order_Data;