Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

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

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

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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

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.

5 Replies
sunny_talwar
Not applicable

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

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.

Damian_Waldron
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Hi Damian

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

Paul

Sent from my iPhone

paulyeo11
Not applicable

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

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