Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

agni_gold
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable

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

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.

4 Replies
Not applicable

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

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
Not applicable

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

ok thanks but if i want fields from multiple tables ?

Not applicable

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

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
Not applicable

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

///$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