Feature: Load current spaces & apps as well as users

I like how it loads all users even if they're not in the events. Useful in environments where you can't load the entire audit log (some places have millions of rows per day).

I've tweaked my version to load spaces & apps as well - mostly just a copy/paste exercise - I'm sure it's not the most efficient.

e.g. for Spaces:

Set vNextURL = '';

[SPACES]:
Load * Inline [ID];

Do
    REST_TABLE:
    SQL SELECT 
        "__KEY_root",
        "__FK_data",

        (SELECT 
            "id",
            "name",
            "type"
        FROM "data" PK "__KEY_data" FK "__FK_data"),
        (SELECT 
            (SELECT 
                "href" as "next"
            FROM "next" FK "__FK_next")
        FROM "links" PK "__KEY_links_u0" FK "__FK_links_u0")
    FROM JSON (wrap on) "root" PK "__KEY_root"
        WITH CONNECTION (  
          URL "https://$(vTenant)/api/v1/spaces?limit=100$(vNextURL)"
        );

    Concatenate([SPACES])
    LOAD    
        [id] as [ID],
        [name] as [Name],
        [type] as [Type]
    RESIDENT REST_TABLE
    WHERE NOT IsNull([__FK_data]);

    NEXTURL:
    Load    
        [next] as NextURL
    Resident REST_TABLE
    Where len([next])>0;

    Drop Table REST_TABLE;

    Let vNextURL = '&' & SubField(Peek('NextURL',0,'NEXTURL'),'?',-1);
    Let vNextURLRows = NoOfRows('NEXTURL');

    Drop Table NEXTURL;

Loop while $(vNextURLRows)>0;

PREPARE_FOR_MAPPING:
LOAD    
    [__KEY_data],
    [Date],
    [eventType]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_data])
and Match([eventType], 'com.qlik.space.created', 'com.qlik.space.deleted')>0;

INNER JOIN(PREPARE_FOR_MAPPING)
LOAD
    [Date],
    [__FK_data] AS [__KEY_data],

    [id_u1] as [ID],
    [email_u1] as [Email],
    [name_u1] as [Name]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);  

//adding in the existing spaces
Concatenate(PREPARE_FOR_MAPPING)
LOAD    
    [ID],
    [Name],
    [Type],
    'com.qlik.space.created' as eventType
RESIDENT SPACES;

MAPPING_SPACE:
MAPPING LOAD
    distinct
    [ID],
    Name //space name
resident PREPARE_FOR_MAPPING
where eventType = 'com.qlik.space.created';

drop tables PREPARE_FOR_MAPPING, SPACES;

kongson-cheung / qlik-saas-application

Feature: Load current spaces & apps as well as users #2