topic Re: How to load multiple files n qlikview from FTP (source)? in QlikView

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

sakshikaul — Thu, 29 Nov 2018 12:06:14 GMT

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 ?

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

marcus_sommer — Thu, 29 Nov 2018 13:49:06 GMT

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

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

sakshikaul — Fri, 30 Nov 2018 05:58:59 GMT

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;

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

marcus_sommer — Fri, 30 Nov 2018 06:15:17 GMT

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

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

sakshikaul — Fri, 30 Nov 2018 06:21:08 GMT

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

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

marcus_sommer — Fri, 30 Nov 2018 08:26:44 GMT

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

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

sakshikaul — Thu, 03 Jan 2019 11:29:26 GMT

what all changes do I have to make in the following syntax ?

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);

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

sakshikaul — Thu, 03 Jan 2019 11:24:59 GMT

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?

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

marcus_sommer — Thu, 03 Jan 2019 11:41:36 GMT

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

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

sakshikaul — Thu, 03 Jan 2019 11:57:25 GMT

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);

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

marcus_sommer — Thu, 03 Jan 2019 12:20:24 GMT

Then is anything wrong with your filepath - you might need to adjust the formatting for the dates and/or the basic-date (if it's not today) and the loop-value and/or excluding weekends or other days which haven't a file and/or including ERRORMODE to skip missing files and/or adding brackets to the filepath if it includes spaces like [$(vFileName)] or the fileformat is different in any way or ... just compare the output from TRACE with your existing files.

- Marcus

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

sakshikaul — Fri, 04 Jan 2019 06:13:35 GMT

My file format on FTP is as follows :-

so I want to make changes accordingly in the script. How to add the timestamp after the date?

for i = 1 to 14
vFileName = FTPServer & Path & File_Prefix & date(today() - i, 'YYYYMMDD') & '.txt';
TRACE $(vFileName);

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

marcus_sommer — Fri, 04 Jan 2019 06:37:28 GMT

Adjusting the date-format would be quite simple with: date(today()-$(i), 'YYYYMMDD') but there is a further numerical index included which looks quite random. This means you would need another loop to handle it.

From a technically point of view it's not too difficult to implement such nested loop which runs through n days and n index-values and tries to load each one as a file and skipped it if it failed but I think using a batch-statement to copy all files to a local storage and using there just a wildcard-load or a filelist() load would be easier and more performant. Therefore take a look on my suggestion from above again:

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

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

sakshikaul — Fri, 04 Jan 2019 07:16:45 GMT

Following code is working fine but still have a confusion....

Folllowing is the Full file load present on Ftp

Material_Master_full:
LOAD distinct
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:userpass16@10.47.77.207/IFR/IAP/QLK/MAT_MAS/MAT_MAS_FULL.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Now I want to run the incremental load (as stated earlier also). The incremental load files are also created on daily basis which contains only the changed record on Ftp in same folder(format is MAT_MAS/MAT_MAS_20190104_000012)

so I want to handle this incremental load in Qlikview ,so for that firstly I need to load the full load file from Ftp and then have to load other files which contains the changed records (present on same Ftp(in same folder where full load is kept).

So how to Implement the same?

What changes should be done in script?

set FTPServer = ftp://ftpIPQ:ftpben16@10.47.77.207;
set Path = /IFR/IAP/QLK/MAT_MAS/;
set File_Prefix = MAT_MAS_;

for i = 1 to 30
vFileName = FTPServer & Path & File_Prefix & date(today(), 'YYYYMMDD_HHmmss') & '.txt';
TRACE $(vFileName);
Concatenate( Material_Master_full)
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);

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

sakshikaul — Fri, 04 Jan 2019 07:32:49 GMT

Following code is working fine but still have a confusion.

I have a query regarding the same. I am having a full load which is present on FTP as follows. Now my main purpose is to implement the increment load and as mentioned earlier also all increment files are also created on daily basis on same ftp in same folder where full load is kept.

Incremental load contains only the changed records. so i want to handle the incremental load in my Qlikview. How to do the same ?

you want me to create another thread for the same or else it will be solved in same thread??

I M IN LEARNING PHASE SO NEED HELP AND WANT TO HANDLE INCREMENTAL LOAD FROM ftp INSTEAD OF LOCAL NETWORK. SO PLEASE HELP?

Material_Master_full:
LOAD distinct
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_FULL.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

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 30
vFileName = FTPServer & Path & File_Prefix & date(today(), 'YYYYMMDD_HHmmss') & '.txt';
TRACE $(vFileName);
Concatenate( Material_Master_full)
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
exit script;

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

marcus_sommer — Fri, 04 Jan 2019 08:42:25 GMT

I think a new thread for the incremental-task would be helpful because it's in generally independent from the source of data and the ftp-stuff will only confuse the readers.

If there are only new records which could be identified on a date an incremental approach is not very difficult but if there are changed and/or deleted records too it's more sophisticated - and to implement it within nested loops over a ftp-source made it not easier.

Therefore I suggest to consider at first if there are possibilities to change the source-data and/or the filenames (do you really need the times within it and/or could dates and times to be included within the data) and/or to reorganize the folder-structure in some way and/or other things which might be reducing the complexity and of the task as well as the needed resources of CPU, RAM, Network and run-times.

In the second half of the following posting you will find several examples and explanations to incremental loadings and exists() to keep qvd-loadings optimized and also the first half will with examples to loops and error-handling be quite helpful for you: Advanced-topics-for-creating-a-qlik-datamodel

- Marcus

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

sakshikaul — Fri, 04 Jan 2019 09:22:38 GMT

Ok thanks,will create another thread for incremental load . As for now following solution is working fine but you can see in the following image it is only picking up the latest file which I have uploaded in the script.

Now how will it work when the new file will be created tomorrow with the new date ie MAT_MAS_20190105_000012.txt. then will it take the current file automatically when the scheduler will run?

Just need to clear this scenario then will close this threadhere is the code and execution script image

set FTPServer = ftp://usrid:userpass@10.47.77.207;
set Path = /IFR/IAP/QLK/MAT_MAS/;
set File_Prefix = MAT_MAS_;

for i = 1 to 30
vFileName = FTPServer & Path & File_Prefix & date(today(), 'YYYYMMDD_HHmmss') & '.txt';
TRACE $(vFileName);

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);

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

marcus_sommer — Fri, 04 Jan 2019 09:37:00 GMT

No, it won't work because you don't cover the _000012 part of the filename. Your used formatting on today() will not return a time-part because there is none and you also couldn't use now() for it else you need a nested loop for this kind of approach, like:

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

and you need to implement ERRORMODE to skip missing files respectively to skip the errors and continue with the next one.

To use such logic within incremental approaches is definitely not easy - therefore my above suggestion to consider each possible simplification before starting the technically work.

- Marcus

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

sakshikaul — Fri, 04 Jan 2019 09:45:16 GMT

can you please provide solution to "Need to implement ERRORMODE to skip missing files respectively to skip the errors and continue with the next one" ? s I am new to Qlik View So need help as I have no idea about the error handling part.

Thanks and Regards

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

sakshikaul — Fri, 04 Jan 2019 09:56:50 GMT

Is this the syntax?

set ErrorMode=0