Avoid Resident Loads and to much tables

[Fabian89]

Hello everyone!

I want to do about 50 controls/checks in Qlik Sense fpr internal revision purposes with data from SAP. I want to use the Qlik Sense Data Editor / Scripting. The basic tables from SAP are loaded in the Qlik Sense app. Then the data will be used for different controls/checks.

For exampe the control/check „EB006 Who changed which field of ... processes?" The data form the table CDHDR will be used. The table CDHDR delivers data for other controls/checks.

For every control the date from the basic table CDHDR will be loaded with Resident Load and. Then these data will be proceed in several stepswith new sub-tables - for example because the data have to be grouped frist (group by), before they can be orderd (order by).

I have many controls which should be done by Qlik sense. Can I do it in a better way?

Thanks a lot!

// Loading the basic tables

[CDHDR]: 

LOAD

           [ID]  as CDHDR.ID,

           [OBJECTCLAS]  as CDHDR.OBJECTCLAS,

           [OBJECTID]  as CDHDR.OBJECTID,

    [CHANGENR]  as CDHDR.CHANGENR,

    [USERNAME]  as CDHDR.USERNAME,

    [UDATE] as CDHDR.UDATE,

    [TCODE]  as CDHDR.TCODE,

    [CHANGE_IND]  as CDHDR.CHANGE_IND

FROM [$(LOADPFAD)/$(LOADOrdner)/$(LOADCDHDR)]

(ooxml, embedded labels, table is Sheet1);

 [CDPOSBELEG]:

LOAD

           ID  as CDPOSBELEG.ID,

    CHANGENR as CDPOSBELEG.CHANGENR,

    [TABNAME]  as CDPOSBELEG.TABNAME,

    FNAME  as CDPOSBELEG.FNAME

FROM [$(LOADPFAD)/$(LOADOrdner)/$(LOADCDPOSBELEG)]

(ooxml, embedded labels, table is Sheet1);

// Doing the controls/checks

// EB006 Who changed which field of ... processes?

 [EB006_CDHDR]:

Load

           [CDHDR.CHANGENR] as EB006.CHANGENR,

    [CDHDR.USERNAME]as EB006.USERNAME01

Resident CDHDR;

Inner Join(EB006_CDHDR)

Load

    CDPOSBELEG.ID as EB006.ID01,

    CDPOSBELEG.CHANGENR as EB006.CHANGENR,

    CDPOSBELEG.TABNAME as CDPOSBELEG.TABNAME01,

    CDPOSBELEG.FNAME as CDPOSBELEG.FNAME01

Resident CDPOSBELEG;

[EB006_not_ordered]:

Load

    EB006.USERNAME01 as EB006.USERNAME02,

    CDPOSBELEG.TABNAME01 as CDPOSBELEG.TABNAME02,

    CDPOSBELEG.FNAME01 as CDPOSBELEG.FNAME02,

    COUNT(EB006.ID01) As EB006.Anzahl01

Resident EB006_CDHDR

GROUp BY EB006.USERNAME01, CDPOSBELEG.TABNAME01, CDPOSBELEG.FNAME01;

[EB006]:

Load

    EB006.USERNAME02 as EB006.USERNAME03,

    CDPOSBELEG.TABNAME02 as CDPOSBELEG.TABNAME03,

    CDPOSBELEG.FNAME02 as CDPOSBELEG.FNAME03,

    EB006.Anzahl01 as EB006.Anzahl02

Resident EB006_not_ordered

ORDER BY 4 ASC;

[marcus_sommer]

The resident-loads for the inner join aren't necessary else could be performed directly against the from-loads. Further I'm not sure if it's really suitable to apply group by and oder by on top of it else this might be done within the UI in any charts.

Beside this you mentioned a lot of tables and most of them to check against a similar logic of how changed when which data. This might be done all together because it's always a date and a user and any item/object - the necessary differentiating information could be stored in an extra field source instead of qualifying everything.