7 Replies Latest reply: Nov 8, 2013 1:20 PM by Agnivesh Kumar RSS

    Want Help in scripting

    Agnivesh Kumar

      I have multiple dimension tables in excel format , I have already loaded data from those files but i want to make a fact table using these dimension tables , how can i load different different columns from different different dimension tables in single load script.

        • Re: Want Help in scripting
          Mario De Stefano

          Hi,

           

          you can use

           

          Qualify *;

           

          in load script before the load of the fact table and

           

          Unqualify *;

           

          at the end of the load of the fact table in the script.

            • Re: Want Help in scripting
              Agnivesh Kumar

              I am sending you a text file in this two tables SHE and one more are present now i want to load some fields from both tables , Suppose both PK and some columns from both tables what would be the script ?

               

               

              QUALIFY *;

              UNQUALIFY %SHE_ID;

              [SHE]:

              LOAD %SHE_ID,

                   Region as [SHE Region],

                   [Programme Group],

                   [Programme Sub-Type],

                   Contractor as [SHE Contractor],

                   [#Hours Worked],

                   [#RIDDOR > 7day],

                   [#RIDDOR > 7day Commentary],

                   [#RIDDOR > 3day],

                   [#RIDDOR > 3day Commentary],

                   [#Dangerous Occurrence],

                   [#Dangerous Occurrence Commentary],

                   [#RIDDOR (Other)],

                   [#RIDDOR (Other) Commentary],

                   [#Lost Time Incident],

                   [#Lost Time Incident Commentary],

                   [#Non-Lost Time Incident],

                   [#Non-Lost Time Incident Commentary],

                   [#Electric Utility Strike],

                   [#Gas Utility Strike],

                   [#Telecom Utility Strike],

                   [#Other Utility Strike],

                   [#Found and Fixed],

                   [#Near Miss]

              FROM

              $(FilePath)\SHE.xls

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

               

               

               

               

               

               

               

               

               

               

               

               

              QUALIFY *;

              UNQUALIFY [%EW No.];

              [CONJECT EWN]:

              LOAD [%EW No.],

                   Title,

                   Detail,

                   [Issue Date],

                   [Raised By],

                   [EW Open/Closed],

                   [Request for Change],

                   [Urgency of Change],

                   [Nature of Change],

                   [Type of Change],

                   [#Anticipated Cost Impact],

                   [Anticipated Programme Impact],

                   [Responses/Actions],

                   [RR No.],

                   [Comments/Decisions],

                   [RR Open / Closed],

                   [#(RR) Anticipated Costs (£)],

                   [#(RR) Anticipated Programme Impact (Days)],

                   [Probability of Risk Occurrence],

                   [Risk Allocation],

                   [PMGCE No.],

                   [PMI No.]

              FROM

              $(FilePath)\CONJECT_EWN.xls

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

            • Re: Want Help in scripting
              Gysbert Wassenaar

              You can't. One load statement can only load from one source table. If you need to combine data from several tables you first need to load one table and then load the next using the join keyword. See: Understanding Join, Keep and Concatenate

                • Re: Want Help in scripting
                  Agnivesh Kumar

                  Thanks for reply

                   

                  I have multiple excel file for multiple tables.

                  In that case i have import all these excel files using script in different tabs in edit script.

                  Now i want to make a table in qlikview that have all the primary keys in different table.

                  How can we do  please help.

                    • Re: Want Help in scripting
                      Agnivesh Kumar

                      I am sending you a text file in this two tables SHE and one more are present now i want to load some fields from both tables , Suppose both PK and some columns from both tables what would be the script ?

                       

                       

                      QUALIFY *;

                      UNQUALIFY %SHE_ID;

                      [SHE]:

                      LOAD %SHE_ID,

                           Region as [SHE Region],

                           [Programme Group],

                           [Programme Sub-Type],

                           Contractor as [SHE Contractor],

                           [#Hours Worked],

                           [#RIDDOR > 7day],

                           [#RIDDOR > 7day Commentary],

                           [#RIDDOR > 3day],

                           [#RIDDOR > 3day Commentary],

                           [#Dangerous Occurrence],

                           [#Dangerous Occurrence Commentary],

                           [#RIDDOR (Other)],

                           [#RIDDOR (Other) Commentary],

                           [#Lost Time Incident],

                           [#Lost Time Incident Commentary],

                           [#Non-Lost Time Incident],

                           [#Non-Lost Time Incident Commentary],

                           [#Electric Utility Strike],

                           [#Gas Utility Strike],

                           [#Telecom Utility Strike],

                           [#Other Utility Strike],

                           [#Found and Fixed],

                           [#Near Miss]

                      FROM

                      $(FilePath)\SHE.xls

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

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                      QUALIFY *;

                      UNQUALIFY [%EW No.];

                      [CONJECT EWN]:

                      LOAD [%EW No.],

                           Title,

                           Detail,

                           [Issue Date],

                           [Raised By],

                           [EW Open/Closed],

                           [Request for Change],

                           [Urgency of Change],

                           [Nature of Change],

                           [Type of Change],

                           [#Anticipated Cost Impact],

                           [Anticipated Programme Impact],

                           [Responses/Actions],

                           [RR No.],

                           [Comments/Decisions],

                           [RR Open / Closed],

                           [#(RR) Anticipated Costs (£)],

                           [#(RR) Anticipated Programme Impact (Days)],

                           [Probability of Risk Occurrence],

                           [Risk Allocation],

                           [PMGCE No.],

                           [PMI No.]

                      FROM

                      $(FilePath)\CONJECT_EWN.xls

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

                    • Re: Want Help in scripting
                      Fernando Keuroglian

                      QUALIFY *;

                      UNQUALIFY %Fecha_Facturación;

                      StokPeriodo:

                      LOAD %Fecha as %Fecha_Facturación,

                           Field0_Material,

                           Field1_Ce.,

                           Field2_Alm.,

                           Field3_Alm,

                           Field4_UMB,

                           [Field5_Libre utiliz.],

                           Field6_Mon.,

                           [Field7_Valor total],

                           Field8_TransyTras,

                           [Field9_Valor total],

                           [Field10_En CtrlCal],

                           [Field11_Valor total],

                           [Field12_No libre],

                           [Field13_Valor no l],

                           Field14_Bloqueado,

                           [Field15_Valor total],

                           Field16_Devol.,

                           Field17_Val.stock,

                           Field18_

                      FROM

                      $(Path_Recursos)StockRepPeriodo*.QVD

                      (qvd);

                       

                      use qualify and unqualify like the example show

                        • Re: Want Help in scripting
                          Agnivesh Kumar

                          I am sending you a text file in this two tables SHE and one more are present now i want to load some fields from both tables , Suppose both PK and some columns from both tables what would be the script ?

                           

                           

                          QUALIFY *;

                          UNQUALIFY %SHE_ID;

                          [SHE]:

                          LOAD %SHE_ID,

                               Region as [SHE Region],

                               [Programme Group],

                               [Programme Sub-Type],

                               Contractor as [SHE Contractor],

                               [#Hours Worked],

                               [#RIDDOR > 7day],

                               [#RIDDOR > 7day Commentary],

                               [#RIDDOR > 3day],

                               [#RIDDOR > 3day Commentary],

                               [#Dangerous Occurrence],

                               [#Dangerous Occurrence Commentary],

                               [#RIDDOR (Other)],

                               [#RIDDOR (Other) Commentary],

                               [#Lost Time Incident],

                               [#Lost Time Incident Commentary],

                               [#Non-Lost Time Incident],

                               [#Non-Lost Time Incident Commentary],

                               [#Electric Utility Strike],

                               [#Gas Utility Strike],

                               [#Telecom Utility Strike],

                               [#Other Utility Strike],

                               [#Found and Fixed],

                               [#Near Miss]

                          FROM

                          $(FilePath)\SHE.xls

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

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                          QUALIFY *;

                          UNQUALIFY [%EW No.];

                          [CONJECT EWN]:

                          LOAD [%EW No.],

                               Title,

                               Detail,

                               [Issue Date],

                               [Raised By],

                               [EW Open/Closed],

                               [Request for Change],

                               [Urgency of Change],

                               [Nature of Change],

                               [Type of Change],

                               [#Anticipated Cost Impact],

                               [Anticipated Programme Impact],

                               [Responses/Actions],

                               [RR No.],

                               [Comments/Decisions],

                               [RR Open / Closed],

                               [#(RR) Anticipated Costs (£)],

                               [#(RR) Anticipated Programme Impact (Days)],

                               [Probability of Risk Occurrence],

                               [Risk Allocation],

                               [PMGCE No.],

                               [PMI No.]

                          FROM

                          $(FilePath)\CONJECT_EWN.xls

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