4 Replies Latest reply: Jul 25, 2012 4:24 AM by Bradley Coyne RSS

    Joining Flatfile and DB table

    Bradley Coyne

      Hi.

       

      Current case is centered around SLA management.

      My mission is to create a Mastertable for Priority + SLA Hours.

      Currently the DB lacks the necessary hours requirements needed for the calculations in the report.

      therefore i am using a flatfile as masterdata record by which i upload to the application.

      Result = Two Tables joined by the PriorityID as a key.

      Expected result = One table that has all the fields joined by PriorityID.

       

      Table 1 below is what already exists in the db in the priority table.

       

      Table1:

       

      Priority IDPriority Name
      1006x
      1007y

       

      Table2 from the flatfile:

       

      PriorityIDSLA_StartSLA_Finish
      1006610
      10071020
      10081430

       

       

       

      Whatthe result should look like.

       

      Priority IDPriority NameSLA_StartSLA_Finish
      1006x610
      1007y1020
      1008z1430

       

      I realize that a join is required however i am not so sure the code for joining a flatfile and DB table how it works.

       

      Any code snippets or advice would be appreciated.

       

      Best,

      Brad

        • Re: Joining Flatfile and DB table

          Hi,

           

          You can load and DB table and flat file into Qlikview first. Say the table names are DBTable and FlatFileTable. Then

           

          FinalTable:

          Left Join(DBTable)

          Load * resident FlatFileTable;

           

          Drop table DBTable;

          Drop table FlatFileTable;

           

          Alternatively, you can use mapping load. Check the reference manual for more information.

           

          Regards,

          Xue Bin

            • Re: Joining Flatfile and DB table
              Bradley Coyne

              Many Thanks Xue Bin!

               

              The Join works well.

              However when dropping both tables -

               

              ->Drop table DBTable;

              ->Drop table FlatFileTable;

               

              The newly created table "FinalTable" disappears from data model.

               

              Here is the script below;

               

              Priority:

              SQL SELECT ID as PriorityID,
              PropertyText as Priority
              FROM EasitManagerSuite.dbo."tblCase_Property";


              Flatfile:
              LOAD PriorityID,
              [SLA  StartTid],
              [SLA Målåtgärdstid]

              FROM
              [..\Desktop\Qlikview - ÄrendehanteringSystem\SLA Table.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);


              PriorityMaster:
              Left join(Priority)
              Load * resident Flatfile;
              DROP Table Priority;
              Drop Table Flatfile;

               


              Best,

              Brad

               

               


               

                • Re: Joining Flatfile and DB table

                  Try this:

                   

                  Priority:

                  SQL SELECT ID as PriorityID,
                  PropertyText as Priority
                  FROM EasitManagerSuite.dbo."tblCase_Property";


                  Flatfile:
                  LOAD PriorityID,
                  [SLA  StartTid],
                  [SLA Målåtgärdstid]

                  FROM
                  [..\Desktop\Qlikview - ÄrendehanteringSystem\SLA Table.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1);

                  Left join(Priority)
                  Load * resident Flatfile;
                  Drop Table Flatfile;

                   

                  I think this also works:

                   

                  Priority:

                  SQL SELECT ID as PriorityID,
                  PropertyText as Priority
                  FROM EasitManagerSuite.dbo."tblCase_Property";


                  Join

                  LOAD PriorityID,
                  [SLA  StartTid],
                  [SLA Målåtgärdstid]

                  FROM
                  [..\Desktop\Qlikview - ÄrendehanteringSystem\SLA Table.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1);