Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
The path is wrong:
FROM $( vToday)[D:\Qlik_OrderSheets\OrderSheets\QLIK*]
It should rather look like:
FROM [D:\Qlik_OrderSheets\OrderSheets\QLIK*$( vToday)*.csv]
- Marcus
Wildcard-loadings aren't possible with FTP. You need to use/create a full-filepath to the file by either duplicating your load-statement or creating them within a loop or you need to move all files to a local/network-storage (maybe with a batch). Here you will find some backgrounds and examples: For Each File in Filelist - FTP load is not working?
- Marcus
Hi
You mean to say like this ?
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://ftpIPQ:ftpben16@10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_FULL.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
concatenate(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://ftpIPQ:ftpben16@10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_20181130_000005.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
store Material_Master_full into C:\Users\dmsadmin\Desktop\New folder\QVD\extract_qvd\Material_Master_Full.qvd;
drop table Material_Master_full;
Yes, just duplicating the load-statements and concatenating the loads into a single table will work. By just a few loadings it's not very elegant but quite practically. If there are dozens or even more loadings then it would be useful to create some kind of loop or to move the files to a local storages - like hinted within the link above.
- Marcus
Hi
Thanks.I have multiple files which are created on daily basis on ftp so I think concatenating the files is not a feasible method to handle these files in qlik view. So , need to use for each loop in this case. I want syntax for the same as the above link is showing some error. Please help
Unfortunately seems this posting currently not be accessable within the new community-release - therefore the link to the old one which I could still access. Here now some copy & paste to the parts I related for:
...
Loads per FTP couldn't have wildcards in path or filename. Also functions like filetime() and filelist() won't be supported from ftp-protocoll. But you could generate a full-path in a variable per algorithm or per in-listing. Alternatively you could use a triggered batch-file (execute statement) with e.g. mget --include "*.csv".
...
...
It will depend from the names of your files. If the names contain the date you could try like this:
for i = 1 to 14
vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.csv';
table:
Load * From $(vFileName) (txt, utf8, embedded labels, delimiter is '\t', msq);
next
...
...
You need always some logic to create a full-path. If this isn't possible or to complex you must transfer all files from ftp to a local or network storage and could then make normal loads with wildcards and so on. For this you need (a triggered) batch-file (per execute statement) with a statement like this:
open YOURSERVER
USER
PASSWORD
lcd "D:\YOURTARGETPATH"
mget --include "*.csv"
bye
exit
Type "ftp" and "batch" in google and you will find many informations and examples.
...
- Marcus
Hi
what all changes do I have to make in the following syntax ?
for i = 1 to 14
vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.csv';
table:
Load * From $(vFileName) (txt, utf8, embedded labels, delimiter is '\t', msq);
next
This is my path and Location as per Ftp
FROM
[ftp://userid:userpassword10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_FULL.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
Hi
can you please elaborate more on this topic ?
I need to use for loop so that multiple files which are created on daily basis can be loaded multiple times from Ftp in qlikview
so How to write and handle the code for the same?
Maybe like this:
set FTPServer = ftp://userid:userpassword@10.47.77.207;
set Path = /IFR/IAP/QLK/MAT_MAS/;
set File_Prefix = MAT_MAS_FULL;
for i = 1 to 14
vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYY/MM/DD') & '.txt';
TRACE $(vFileName);
// table: Load * From $(vFileName) (txt, codepage is 1252, embedded labels, delimiter is '|', msq);
next
At first comment the load and look on the returns of TRACE (in log-file and/or progress-window) to adjust/ensure that your logic really returns a valid filepath.
- Marcus
Hi
without loading a file its functioning
but when I uncomment the table (refer following) its giving an error.
table: Load * From $(vFileName) (txt, codepage is 1252, embedded labels, delimiter is '|', msq);