Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
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's the name of the picklist (minus special characters and spaces).




// 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
     ITEMID     as PicklistIDs,
    TEXT    as PicklistNames
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);    
            TEXT As [$(vMyPicklist)];
            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