How to make use of FOR and NEXT to reduce 10 load script into 1 ?

[paulyeo11]

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

[sunny_talwar · Accepted solution]

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.

[Anonymous]

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

[paulyeo11]

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

[paulyeo11]

Hi Damian

Thank you for your sharing . If using loop do I need to change TDSS to number ?

Paul

Sent from my iPhone

[paulyeo11]

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