why do we need to avoid auto number function while QVD creation

[kishorj1982]

Hi All,

May I know why we need to avoid auto number function while QVD creation.

Thanks,

Kishor.

[jobsonkjoseph]

Hi Kishor,

This might help you,

Autonumber Handling for QVD Optimized Load (via Key Storage)

[avkeep01]

Hi Kishor,

When you autonumber fields in a table in one QVW and store into QVD, and you autonumber fields in a table in another QVW and store it in another QVD, then autonumber isn't related to each other. So when loading both QVD's into a new QVW the keys aren't matched. When using autonumber you should do that on all the tables in one QVW, but you can't do this in seperate QVW's.

Btw here is a script to autonumber all fields starting with % and ending with _key.

/* ---- SETTING ---- */

SET vKeyField = %;

SET vException = RawMaterials.Waste %;

LET vException = CHR(39)&REPLACE('$(vException)',',',CHR(39)&','&CHR(39))&CHR(39);

FOR i = 0 TO NUM(NOOFTABLES()-1);

Tables_ZYX:

LOAD

TABLENAME('$(i)') AS Table

AUTOGENERATE(1);

NEXT i

FOR i = 0 TO NUM(NOOFROWS('Tables_ZYX')-1);

LET vTable = PEEK('Table',$(i),'Tables_ZYX');

FOR a = 1 TO NUM(NOOFFIELDS('$(vTable)'))

LET vField = FIELDNAME($(a),'$(vTable)');

IF $(a) = 1 THEN

LET vTableScript = '[$(vTable)_AUTONUMBER]: NOCONCATENATE LOAD '&IF(SUBSTRINGCOUNT('$(vField)','$(vKeyField)')>0 AND MATCH('$(vField)',$(vException))=0,'AUTONUMBER("$(vField)",'&CHR(39)&'$(vField)'&CHR(39)&') AS "$(vField)"','"$(vField)"');

'$(vKeyField)'

ELSE

LET vTableScript = vTableScript&', '&IF(SUBSTRINGCOUNT('$(vField)','$(vKeyField)')>0 AND MATCH('$(vField)',$(vException))=0,'AUTONUMBER("$(vField)",'&CHR(39)&'$(vField)'&CHR(39)&') AS "$(vField)"','"$(vField)"');

END IF

// Next field

NEXT a

LET vTableScript = vTableScript&' RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; RENAME TABLE [$(vTable)_AUTONUMBER] TO [$(vTable)]' ;

TRACE ;

TRACE >>> LOAD $(vTable)_AUTONUMBER AND RENAME TO $(vTable);

// Execute script

$(vTableScript);

// reset script

LET vTableScript = '';

// next table

NEXT i

DROP TABLE Tables_ZYX;

[avkeep01]

I've also created a short version of this script. This is without the exception handling which is available in the script above.

FOR a = 0 TO NUM(NOOFTABLES()-1);

Tables_ZYX:

LOAD

TABLENAME('$(a)') AS Table

AUTOGENERATE(1);

NEXT a

FOR a = 0 TO NUM(NOOFROWS('Tables_ZYX')-1);

FOR b = 1 TO NUM(NOOFFIELDS(PEEK('Table',$(a),'Tables_ZYX')))

LET vTableScript = IF($(b)=1,'','$(vTableScript) ')&IF(SUBSTRINGCOUNT(FIELDNAME('$(b)',PEEK('Table',$(a),'Tables_ZYX')),'%')>0,'AUTONUMBER("'&FIELDNAME('$(b)',PEEK('Table',$(a),'Tables_ZYX'))&'",'&CHR(39)&FIELDNAME('$(b)',PEEK('Table',$(a),'Tables_ZYX'))&CHR(39)&') AS "'&FIELDNAME('$(b)',PEEK('Table',$(a),'Tables_ZYX'))&'"','"'&FIELDNAME('$(b)',PEEK('Table',$(a),'Tables_ZYX'))&'"')&',';

NEXT b

LET vTableScript = '['&PEEK('Table',$(a),'Tables_ZYX')&'_AUTONUMBER]: NOCONCATENATE LOAD '&LEFT('$(vTableScript)',LEN('$(vTableScript)')-1)&' RESIDENT ['&PEEK('Table',$(a),'Tables_ZYX')&']; DROP TABLE ['&PEEK('Table',$(a),'Tables_ZYX')&']; RENAME TABLE ['&PEEK('Table',$(a),'Tables_ZYX')&'_AUTONUMBER] TO ['&PEEK('Table',$(a),'Tables_ZYX')&']' ;

$(vTableScript);

NEXT a

DROP TABLE Tables_ZYX;