Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Replace load issue

I want to use the following script in a Partial Reload, but if you add the REPLACE function the script doesn't perform the same way.

Can anyone see why or a way around the problem?

Without 'Replace' (works well)

//====================================================================

Table1:

load * inline [

ID,name,type,value

1,A,blue,10

1,A,yellow,50

3,C,red,80

3,C,yellow,60

4,D,yellow,40

];

Table2:

generic load *

resident Table1;

ResultTable:

load Distinct ID, name Resident Table1;

FOR i = 0 to NoOfTables()

  TableList:

  load TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'Table2.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) load * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

Drop Tables Table1, TableList;

//====================================================================

With 'Replace' (doesn't work)

//====================================================================

Table1:

replace load * inline [

ID,name,type,value

1,A,blue,10

1,A,yellow,50

3,C,red,80

3,C,yellow,60

4,D,yellow,40

];

Table2:

generic replace load *

resident Table1;

ResultTable:

replace load Distinct ID, name Resident Table1;

FOR i = 0 to NoOfTables()

  TableList:

  replace load TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'Table2.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) replace load * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

Drop Tables Table1, TableList;

//====================================================================

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

I amened the script and this seems to work well.

IF IsPartialReload()=-1 THEN

     DROP TABLE ResultTable;

END IF

Table1:

replace load * inline [

ID,name,type,value

1,A,blue,10

1,A,yellow,50

3,C,red,80

3,C,yellow,60

4,D,yellow,40

];

Table2:

GENERIC REPLACE LOAD * RESIDENT Table1;

ResultTable:

REPLACE LOAD DISTINCT ID, name RESIDENT Table1;

FOR i = 0 to NoOfTables() 

     NoConcatenate

     TableList$(i):

     REPLACE LOAD TableName($(i)) AS Tablename AUTOGENERATE 1

     WHERE WildMatch(TableName($(i)), 'Table2.*');

NEXT i

LET ii = $(i)-1;

FOR i = 1 to FieldValueCount('Tablename')

     LET vTable = FieldValue('Tablename', $(i));

     LEFT JOIN (ResultTable) REPLACE LOAD * RESIDENT [$(vTable)];

     DROP TABLE [$(vTable)];

NEXT i

DROP TABLE Table1;

FOR i = 0 to ii

     DROP TABLE TableList$(i);

NEXT i;

View solution in original post

4 Replies
dclark0699
Creator
Creator

Your issue is here:

FOR i = 0 to NoOfTables()

  TableList:

  replace load TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'Table2.*');

NEXT i

This doesn't build the list of generic tables because it is getting replaced during each iteration of the loop. So in the end you only end up with one record in this table and it only joins one of the generic tables to your final table.

Is there a reason you are using the replace? Is it for partial reloading? I think because it is a table you end up dropping, you could use the add keyword instead of replace and be ok

Anonymous
Not applicable
Author

Good point.

Yes it is for partial reloading for Section Access so i can always load my latest security file even if the application data has not changed.

Anonymous
Not applicable
Author

I amened the script and this seems to work well.

IF IsPartialReload()=-1 THEN

     DROP TABLE ResultTable;

END IF

Table1:

replace load * inline [

ID,name,type,value

1,A,blue,10

1,A,yellow,50

3,C,red,80

3,C,yellow,60

4,D,yellow,40

];

Table2:

GENERIC REPLACE LOAD * RESIDENT Table1;

ResultTable:

REPLACE LOAD DISTINCT ID, name RESIDENT Table1;

FOR i = 0 to NoOfTables() 

     NoConcatenate

     TableList$(i):

     REPLACE LOAD TableName($(i)) AS Tablename AUTOGENERATE 1

     WHERE WildMatch(TableName($(i)), 'Table2.*');

NEXT i

LET ii = $(i)-1;

FOR i = 1 to FieldValueCount('Tablename')

     LET vTable = FieldValue('Tablename', $(i));

     LEFT JOIN (ResultTable) REPLACE LOAD * RESIDENT [$(vTable)];

     DROP TABLE [$(vTable)];

NEXT i

DROP TABLE Table1;

FOR i = 0 to ii

     DROP TABLE TableList$(i);

NEXT i;