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

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

kishorj1982 — Tue, 23 Oct 2018 06:43:25 GMT

Hi All,

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

Thanks,

Kishor.

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

jobsonkjoseph — Tue, 23 Oct 2018 06:49:14 GMT

Hi Kishor,

This might help you,

Autonumber Handling for QVD Optimized Load (via Key Storage)

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

avkeep01 — Tue, 23 Oct 2018 07:11:13 GMT

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;

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

avkeep01 — Tue, 23 Oct 2018 07:18:31 GMT

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;