generate fact table using already loaded tables not from any source file or database

[agni_gold]

Hi

I need a help regarding Qlikview scripting.

i have multiple tables and i have already loaded these table in qlikview .

Now i want to make a fact table using these loaded tables not from any source file .

Can it be possible ?

If yes then please tell me how , please provide some sample code to understand.

Thanks.

Best Regards ,

Agnivesh Kumar

[#2 · Accepted solution]

By RESIDENT key word, you can load the data from already loaded table. Please fine below sample

TABLE1:

SELECT * FROM DBSOURCETABLE;

FACTTABLE:

LOAD FILED1,FIELD2, FIELD3

RESIDENT TABLE1;

But I would recommend create the intermediate qvd's rather than RESIDENT if the table have huge data.

[agni_gold]

ok thanks but if i want fields from multiple tables ?

[#4]

You can concatenate or join the different Resident Loads to create the fact table.

Please post some data or script so we will little more

[agni_gold]

///$tab Main

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET FilePath='C:\Users\agnivesh\Desktop\Desktop\SDM DATA SHEETS';

///$tab CONJECT_EWN

[CONJECT EWN]:

LOAD [%EW No.],

     Title,

     Detail,

     [Issue Date],

FROM

$(FilePath)\CONJECT_EWN.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab CONJECT_PMC

[CONJECT PMC]:

LOAD [%PMC Nr],

     [Issued Date],

     [#PMC Accepted Cost  (£)],

     Description

FROM

$(FilePath)\CONJECT_PMC.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab CUST

[CUST]:

LOAD %Period_Del_ID,

     Delivery_Area

FROM

$(FilePath)\CUST.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab KSA_AUDIT

[KSA AUDIT]:

LOAD [%Audit ID],

     Comments,

     [Improvement Suggestions]

FROM

$(FilePath)\KSA_AUDIT.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab KSA_QUESTION

[KSA QUESTION]:

LOAD [%Question ID],

     Question

FROM

$(FilePath)\KSA_QUESTION.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab NC_SCREEN_ACCOUNT

[NC SCREEN ACCOUNT]:

LOAD [%ADM AccountNo],

     [ADM SchemeLink],

     [FC Status]

FROM

$(FilePath)\NC_SCREEN_ACCOUNT.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab NC_SCREEN_CONNECTIONS

[NC SCREEN CONNECTIONS]:

LOAD %ADM_SchemeNo,

     CONT_LiveAccountRef,

     ADM_SiteName,

     ADM_Designer_Assigned

FROM

$(FilePath)\NC_SCREEN_CONNECTIONS.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab P6_ACTIVITY

[P6 ACTIVITY]:

LOAD [%Activity ID],

     [Activity Name],

     Start,

     Finish

FROM

$(FilePath)\P6_ACTIVITY.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab P6_PROJECT

[P6 PROJECT]:

LOAD [%Project ID],

     [Project Name]

FROM

$(FilePath)\P6_PROJECT.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab P6_RESOURCE

[P6 RESOURCE]:

LOAD [%Resource ID],

     [#Budgeted Units]

FROM

$(FilePath)\P6_RESOURCE.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab P6_RESOURCE_PROFILE

[P6 RESOURCE PROFILE]:

LOAD [%Resource Profile ID],

     [Resource ID Name]

FROM

$(FilePath)\P6_RESOURCE_PROFILE.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab PERIOD

[PERIOD]:

LOAD %Period_ID,

     [P Month],

     [P Year],

     [P Financial Quarter],

     [P Financial Year]

FROM

$(FilePath)\PERIOD.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab PROFILE_PERIOD

[PROFILE PERIOD]:

LOAD [%Profile Period ID],

     [PP Month],

     [PP Year],

     [PP Financial Year]

FROM

$(FilePath)\PROFILE_PERIOD.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab R12

[R12]:

LOAD [%Expenditure ID],

     [Task Number],

     [#Expenditure Amount]

FROM

$(FilePath)\R12.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab SHE

[SHE]:

LOAD %SHE_ID,

     Region as [SHE Region],

     [Programme Group],

     [#Near Miss]

FROM

$(FilePath)\SHE.xls

(biff, embedded labels, table is [Sheet1$]);

///$tab Generate Fact tables from dimension tables

these are some loaded tables i want to make a fact table from these and want some fields from each table