Re: List objects in Sheets - Qlik Community

robert_mika · ‎2015-04-20

Is there a way to have a list of all objects(let say in Textbox) that are currently in the file?

Let say comma separated ' 'CH01','LB01'....

Anonymous · ‎2015-04-20

Try starting from here, it should help:

sub test
set sheet = ActiveDocument.Sheets("SH01")
For i = 0 to 50

set so = sheet.SheetObjects(i)
id = so.GetObjectId
msgbox(id)

next
end sub

robert_mika · ‎2015-04-20

is the a way to pass that to TextBox?

Anonymous · ‎2015-04-20

What you can do in a macro is to assign result to a variable. After this you can show it in a text box of course.

marcus_sommer · ‎2015-04-20

You could also read the meta-data from the qvw and from layout-export and use these as tables and fields:

/****************************************************************************************************************************************************************
Beginn DocumentsObjects - Auslesen Attribute SOE (Sheet, Objects & Expressions + Groups, Reports, Bookmarks, Variables) aus QVW's (Metadaten XML-Struktur)
*****************************************************************************************************************************************************************

Aufgabe:
- Lädt die ausgewählte Meta-Daten dieser Anwendung ein

Hintergrund:
- schneller und direkter Zugriff auf die Objektstruktur dieser Anwendung um die Sheet-Oberflächen besser entwickeln zu können, z.B. welches Objekt befindet sich
  auf welchem Sheet
- QlikView-Anwendungen sind eine Mischung zwischen propertieren Datenstrukturen und XML-Datenstruktur, so daß nicht alle Meta-Daten aus der Anwendung selbst
  ausgelesen werden können --> die hier fehlenden Meta-Daten können aber über den Export von Struktur-und Layout-Daten anschließend wieder eingelesen werden
- Export dieser Daten kann Zentral über ein separates Steuerungsfile und/oder direkt in dieser Anwendung per Makro ausgelöst werden (nicht alle Datenbereiche
  sind GUI erreichbar)

Datenquelle:
- diese Anwendung selbst --> Documentname()
- ....\DataStructures\AppLayout\AppLayout_' & DocumentName() & '.xml'

ToDo:
- Aufnahme weiterer Meta-Daten, z.B. Mem-Files

---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

let vTabName = 'DocumentObjects';
let vOwnPath = DocumentPath(); // Pfadvariable - eigener Pfad
let vLayoutPath = '$(pApp@01)' & 'DataStructures\AppLayout\AppLayout_' & DocumentName() & '.xml'; // Pfadvariable zum Layout-Source-File
let vLayoutStoredPath = '$(pApp@01)' & 'DataStructures\AppLayoutQVD\' & DocumentName(); // Pfadvariable zum Layout-Stored-File

if '$(vLoadDocumentObjects)' = 'current' then

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------
GROUPS auslesen - nicht direkt an SOE gejoint, da der Name zur Gruppe gejoint wird
---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    Groups:
    LOAD upper(filepath()) as FileName, 'Group' as SOE_Type, Name as %ObjectId, if(IsCyclic='true','Cyclic','Hierarchic') as Definition,
         filetime() as CreatedDate, %Key_GroupDescription_CB92F98E8506AF37    // Key for this table: DocumentSummary/GroupDescription
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/GroupDescription]);
        join 
    LOAD Name as Label, %Key_GroupDescription_CB92F98E8506AF37    // Key to parent table: DocumentSummary/GroupDescription
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/GroupDescription/FieldDefs/FieldDefEx]);
    drop field %Key_GroupDescription_CB92F98E8506AF37;
    $(Include=$(pScript@04)Include_LoadLogging.txt);

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------
SOE's auslesen
---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    SheetObjects: 
    NoConcatenate Load * Resident Groups; // Groups
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, SheetId as %ObjectId, 'Sheet' as SOE_Type, Title as Label // Sheets
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Sheet]);
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, REPLACE(ObjectId,'Document\','') as %ObjectId, Type as SOE_Type, Caption as Label // Sheet Objects
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/SheetObject]);
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, %ObjectId, 'Expression' as SOE_Type, Definition, Label // Expressions
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Expression]);
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, %ObjectId, 'Dimension' as SOE_Type, PseudoDef as Definition, Title as Label // CalculatedDimension
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Dimension]);
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, %ObjectId, 'Variable' as SOE_Type, RawValue as Definition, Name as Label // VariableDescription
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/VariableDescription]);
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, Id as %ObjectId, 'Report' as SOE_Type, Name as Label // Report
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Report]) WHERE Id > '';
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, BookmarkId as %ObjectId, 'Bookmark Item' as SOE_Type, BookmarkField as Label // BookmarkItems
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/BookmarkItems]);
        OUTER JOIN 
    LOAD upper(filepath()) as FileName, BookmarkId as %ObjectId, 'Bookmark' as SOE_Type, Name    as Label // Bookmark
    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Bookmark]);
    store SheetObjects into $(vLayoutStoredPath)_SheetObjects.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);
    drop table Groups;

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------
Zuordnung von Objekten zum Sheet generieren --> aus dem exportierten App_Layout
---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    FrameParentDef:
    LOAD replace(ObjectId, 'Document\','') as %ObjectId, %Key_SheetProperties_CD0C067A8D4AC977
    FROM $(vLayoutPath) (XmlSimple, Table is [DocLayout/AllProperties/LayoutProperties/LayoutProperties/SheetProperties/ChildObjects/FrameParentDef]);
    store FrameParentDef into $(vLayoutStoredPath)_FrameParentDef.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);
    
    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    SheetProperties:
    LOAD Name as SheetName, replace(SheetId, 'Document\','') as SheetId, //'Sheet' as SOE_Type,
        %Key_SheetProperties_CD0C067A8D4AC977    // Key for this table: DocLayout/AllProperties/LayoutProperties/LayoutProperties/SheetProperties
    FROM $(vLayoutPath) (XmlSimple, Table is [DocLayout/AllProperties/LayoutProperties/LayoutProperties/SheetProperties]);
    store SheetProperties into $(vLayoutStoredPath)_SheetProperties.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);
    
elseif '$(vLoadDocumentObjects)' = 'stored' then

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    SheetObjects: 
    Load * From $(vLayoutStoredPath)_SheetObjects.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    FrameParentDef:
    Load * From $(vLayoutStoredPath)_FrameParentDef.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    SheetProperties:
    Load * From $(vLayoutStoredPath)_SheetProperties.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);
    
end if

/****************************************************************************************************************************************************************
Ende DocumentObjects
****************************************************************************************************************************************************************/





let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging



    SheetObjects: 
    Load * From $(vLayoutStoredPath)_SheetObjects.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    FrameParentDef:
    Load * From $(vLayoutStoredPath)_FrameParentDef.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging
    SheetProperties:
    Load * From $(vLayoutStoredPath)_SheetProperties.qvd (qvd);
    $(Include=$(pScript@04)Include_LoadLogging.txt);
    
end if

/****************************************************************************************************************************************************************
Ende DocumentObjects
****************************************************************************************************************************************************************/

- Marcus

robert_mika · ‎2015-04-21

Thanks Marcus but how this by used in a way to give all objects in sheets?

Anonymous · ‎2015-04-21

Hi Robert

in the same directory where you application is create a folder and name it like so "AppName-prj"

save open and save your App

if you open the prj folder u will see a number of xml files created

place script somewhere on you App in the load script or create a separate App for testing purposes and place the following code in the load script

make use of fields generated

if u have any queiries let me know

Set vFolder = 'Path to your prj folder\test-prj';

for each File in filelist(vFolder& '\*CH0*.xml') //file names of all objects you want to bring in

EXPRESSIONDATA:

LOAD

RowNo() as Position, filename() as fileName,

[GraphLayout/Frame/Name/v] as ChartName,

[_ExpressionVisuals/ExpressionVisualDef/Label/v] as ExpressionName,

[_ExpressionDatas/ExpressionData/Definition/v]as ExpressionCalc,

[_ExpressionVisuals/ExpressionVisualDef/Label/v]

&': ' &[_ExpressionDatas/ExpressionData/Definition/v]

as

Expression

,

left(filename(),4) as ObjectID

FROM $(vFolder)\*CH0*.xml (XmlSimple, Table is [GraphProperties]);

// End of [CH01.xml] LOAD statements

CHARTDATA:

LOAD

[ChartProperties/Title/Title/v]as ChartTitle,

[TableProperties/ExpansionInfo/ExpansionData/Pos/DimName] as DimensionName,

[Dimensions/ChartDimensionDataDef/PseudoDef/Name] as DimensionCalc

,

left(filename(),4) as ObjectID

FROM $(vFolder)\*CH0*.xml (XmlSimple, Table is [GraphProperties]);

// End of [CH01.xml] LOAD statements

exit Script;

left

join

(CHARTDATA) load

ObjectID

,replace

(ObjectID

, 'CH','') as

CHTID

, concat

(distinct

DimensionCalc

) as

DimensionsConcat

REsident

CHARTDATA group

by

ObjectID

;

left

join

(EXPRESSIONDATA) load

ObjectID

,

concat

(distinct

Expression

,'

' ) as

ExpressionsConcat

REsident

EXPRESSIONDATA group

by

ObjectID

;

marcus_sommer · ‎2015-04-29

My approach includes more than the object-id's and therefore the script is a bit longer and more complex. But you could have deeper insights in your apps. Results from my script are:

- Marcus