Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

QlikView QVD Generator for all Picklist Lists

So you need a Picklist list ListBox to go after MultiPick List valued database columns (using a match expression)...I needed about 8 picklist ListBoxes so decided to generate ALL picklists as QVD files....if you want other columns you could certainly add them in.....note that the name of the field is no longer TEXT....it's the name of the picklist (minus special characters and spaces).

 

Fast!

 

// Load/Store as QVD files:
// SalesLogix Picklist table
// break out the picklists for use in ListBoxes
// June 18, 2013 RJ Samp

// Added Deepak Vadithala's [QlikCare] For Next Loop to read all SLX ResynchTableDefs TableNames.
// ======================================================//
 
 
Sub LoadPicklistNames
 PickListList:
 SQL
 Select
     ITEMID     as PicklistIDs,
    TEXT    as PicklistNames
 FROM
    sysdba.PICKLIST
 WHERE
    PICKLISTID = 'PICKLISTLIST'
    ORDER BY TEXT;
End Sub;
 
// ======================================================//
 
Call LoadPicklistNames;
 
 
Let vPicklistCount = NoOfRows('PickListList') - 1;
 
// ======================================================//

Sub LoadPicklistData
    For i = 0 To $(vPicklistCount)
    // set variablename = string
    // let variable     = expression
    // $(variablename) is a macro expansion, i.e. whatever is the value of the variable is evaluated as script text.

        LET vMyTableName               = Peek('PicklistNames', $(i), 'PickListList');
        vMyTableName                        = purgechar(vMyTableName, Chr(39));
        LET vMyPickListID                   = Peek('PicklistIDs',     $(i), 'PickListList');
        LET vMyListTableName         =
            Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace('$(vMyTableName)',' ', ''),'(',''),')',''),'-',''),'_',''), '.',''), '/',''), '\',''), '&','') & 'List';
        Let vMyPicklist                = Left(vMyListTableName, Len(vMyListTableName) - 4);    
        [$(vMyListTableName)]:
        LOAD
            TEXT As [$(vMyPicklist)];
         SQL
         Select
              TEXT
         FROM
            sysdba.PICKLIST
        WHERE
            PICKLISTID = '$(vMyPickListID)'
            ORDER BY TEXT;
        STORE $(vMyListTableName) INTO $(QVDSLXPath)\$(vMyListTableName).qvd (qvd);    
        DROP Table $(vMyListTableName);
        Next i
End Sub;
 
// ==========================================================================================//
 
Call LoadPicklistData;
 
// ==========================================================================================//

Drop Table PickListList;

RJ Samp