Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have below 2 sample load script . in fact my actual script i have 10 similar data structure. may i know how to make use of
For and Next command to reduce the overall 10 load into 1 ?
GL_TABLE: | // first line of the code is to give the table a name |
LOAD | // Load meaning to read the raw data file. | ||||||||||||||||||
'TDSS' as SOURCE, | |||||||||||||||||||
date#(@58:77T,'DD/MM/YYYY') as date, | |||||||||||||||||||
AutoNumberHash128(@58:77T,'TDSS') As Link_GL_Key, | |||||||||||||||||||
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount] // This will join the sign and AMT and create new var = AMOUNT. |
FROM $(vRAWPath)$(vFile63) (ansi, fix, no labels, header is 0, record is line);
Concatenate
LOAD | // Load meaning to read the raw data file. | ||||||||||||||||||
'TDSKL' as SOURCE, | |||||||||||||||||||
date#(@58:77T,'DD/MM/YYYY') as date, | |||||||||||||||||||
AutoNumberHash128(@58:77T,'TDSKL') As Link_GL_Key, | |||||||||||||||||||
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount] // This will join the sign and AMT and create new var = AMOUNT. |
FROM $(vRAWPath)$(vFile64) (ansi, fix, no labels, header is 0, record is line);
Something along this line:
GL_TABLE:
LOAD 0 as Dummmy
AutoGenerate 0;
FOR i = 63 to 72
Concatenate (GL_TABLE)
LOAD 'TDSS' as SOURCE,
Date#(@58:77T,'DD/MM/YYYY') as date,
AutoNumberHash128(@58:77T,$(i)) As Link_GL_Key,
If(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM $(vRAWPath)$(vFile$(i))
(ansi, fix, no labels, header is 0, record is line);
NEXT i;
The thing to note is that you will have to somehow dynamically get the SOURCE because I don't have access to your actual data.
Something along this line:
GL_TABLE:
LOAD 0 as Dummmy
AutoGenerate 0;
FOR i = 63 to 72
Concatenate (GL_TABLE)
LOAD 'TDSS' as SOURCE,
Date#(@58:77T,'DD/MM/YYYY') as date,
AutoNumberHash128(@58:77T,$(i)) As Link_GL_Key,
If(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM $(vRAWPath)$(vFile$(i))
(ansi, fix, no labels, header is 0, record is line);
NEXT i;
The thing to note is that you will have to somehow dynamically get the SOURCE because I don't have access to your actual data.
Do you need to loop? You can use a * to indicate to Qlik to load all files.
I.e.
FROM $(vRAWPath)*(ansi, fix, no labels, header is 0, record is line);
Hi sunny
This is a good hint for me to kick start. As I can change TDSS to number and also file name to number.
I will try it.
Paul
Sent from my iPhone
Hi Damian
Thank you for your sharing . If using loop do I need to change TDSS to number ?
Paul
Sent from my iPhone
Hi Sunny
I have below script which is runing well :-
SET vDevelopment = 0;
IF $(vDevelopment) = 0 THEN
SET vRAWPath = 'D:\AUTO_LOAD\'; //local folder
SET vFile50 = '60';
set vFile51 = '61';
else
END IF
GL_TABLE:
LOAD
'TDSS' as SOURCE_GL,
'60' as SOURCE,
date#(@58:77T,'DD/MM/YYYY') as date,
AutoNumberHash128(@58:77T,'60') As Link_GL_Key,
@124:129T as [Reporting Code],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);
Concatenate
LOAD
'TDSID' as SOURCE_GL,
'61' as SOURCE,
date#(@58:77T,'DD/MM/YYYY') as date,
AutoNumberHash128(@58:77T,'61') As Link_GL_Key,
@124:129T as [Reporting Code],
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM $(vRAWPath)$(vFile52).txt (ansi, fix, no labels, header is 0, record is line);
When press the intial select , i expect to get -111135.02
// BELOW IS I TRY BUT GET WRONG VALUE
GL_TABLE:
LOAD 0 as Dummmy
AutoGenerate 0;
FOR i = 60 to 61
Concatenate (GL_TABLE)
LOAD
'$(i)' as SOURCE,
'TDSS' as SOURCE_GL,
Date#(@58:77T,'DD/MM/YYYY') as date,
AutoNumberHash128(@58:77T,$(i)) As Link_GL_Key,
@124:129T as [Reporting Code],
If(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]
FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);
NEXT i;
I get the wrong result . May i know why ?
I have enclosed a simple file in the zip file , pls unzip it and copy to Drive D , folder name AUTO.
Paul