Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
evason75
Contributor

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;

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

Tags (1)
1 Solution

Accepted Solutions
evason75
Contributor

Re: Replace load issue

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;

4 Replies

Re: Replace load issue

dclark0699
Contributor

Re: Replace load issue

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

evason75
Contributor

Re: Replace load issue

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.

evason75
Contributor

Re: Replace load issue

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;

Community Browser