Solved: How to load multiple files n qlikview from FTP (s... - Page 2 - Qlik Community

sakshikaul · ‎2018-11-29

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 ?

marcus_sommer · ‎2019-01-03

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

sakshikaul · ‎2019-01-04

Hi

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

marcus_sommer · ‎2019-01-04

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

sakshikaul · ‎2019-01-04

Hi

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

sakshikaul · ‎2019-01-04

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;

marcus_sommer · ‎2019-01-04

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

sakshikaul · ‎2019-01-04

Hi

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

marcus_sommer · ‎2019-01-04

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

sakshikaul · ‎2019-01-04

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

sakshikaul · ‎2019-01-04

Is this the syntax?

set ErrorMode=0

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