4 Replies Latest reply: Nov 9, 2013 1:39 PM by Agnivesh Kumar RSS

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

    Agnivesh Kumar

      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

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

          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.

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

              ok thanks but if i want fields from multiple tables ?

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

                  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

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

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