topic Re: Replace load issue in QlikView

Replace load issue

Anonymous — Mon, 14 Sep 2015 14:13:45 GMT

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;

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

Re: Replace load issue

marcus_sommer — Mon, 14 Sep 2015 14:25:04 GMT

Maybe this Re: Partial Reload: REPLACE/ADD LOAD returns script errors before loading and this Re: Add, Add Only and Replace, Replce Only is helpful for you.

- Marcus

Re: Replace load issue

dclark0699 — Mon, 14 Sep 2015 14:25:30 GMT

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

Re: Replace load issue

Anonymous — Mon, 14 Sep 2015 15:27:10 GMT

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.

Re: Replace load issue

Anonymous — Tue, 15 Sep 2015 07:11:49 GMT

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;