Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
ok thanks but if i want fields from multiple tables ?
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
///$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