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: 
kishorj1982
Creator II
Creator II

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

Hi All,

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

Thanks,

Kishor.

3 Replies
jobsonkjoseph
Creator III
Creator III

avkeep01
Partner - Specialist
Partner - Specialist

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
Partner - Specialist
Partner - Specialist

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;