1 Reply Latest reply: Nov 7, 2012 9:23 AM by Gysbert Wassenaar RSS

    Preparation / Loading of Data - General approach

    Michael Ionkin

      Hello QlikCommunity

       

      I don't know what is the right way to load and cleanse the data.

       

      What I did first is to load the data 1:1 and store it in qvd s.

      Then I load the qvd s and join, cleanse .. the data.

       

      Here is some code to show what i mean:

       

      -------------------------------------------------------------------------------------CODE-----------------------------------------------------------------------------

      Register 1 :

       

      TOL_NEW:
      LOAD lTolRef,
          
      lTolTypRef,
          
      sDepot,
          
      sDescript,
          
      sNameFROM
      ..\..\02_Data\TOL_MI.qvd
      (
      qvd)Where ( (lTolTypRef = 10) OR (lTolTypRef = 20) OR (lTolTypRef = 30) )
      ;

      Left Join
      LOAD
           
      lTolRef,
           
      lJobRef//      , 
      //     lTolTypRef,
      //      tBeginFROM
      ..\..\02_Data\TOL_BOK_MI.qvd
      (
      qvd);

       

       

      Store

      TOL_NEW into

      ..\..\02_Data\TOL_New_MI.qvd;

       

      Redister 2:

       

      JOB_NEW:

      LOAD
      dSetQuant,
          
      dSetShots,
          
      dSetSpeed,
          
      iActCavity,
          
      iSetCavity,
          
      lJobRef,
          
      sName as Auftragsnummer,
          
      sType,
          
      tActBegin,
          
      tActEnd,
          
      tReleaseDate
      FROM
      ..\..\02_Data\JOB_MI.qvd
      (
      qvd);

      Left Join

      LOAd


            
      lTolRef,
          
      lTolTypRef,
          
      sDepot,
          
      sDescript,
          
      sName as TOL_sName,
          
      lJobRef
          
      From ..\..\02_Data\TOL_New_MI.qvd
      (
      qvd);

      Store JOB_NEW
      into ..\..\02_Data\JOB_New_MI.qvd;

       

       

      Register 3:

       

       

      LOAD dSetQuant,
          
      dSetShots,
          
      dSetSpeed,
          
      iActCavity,
          
      iSetCavity,
          
      lJobRef,
          
      Auftragsnummer,
          
      sType,
          
      tActBegin,
          
      tActEnd,
          
      tReleaseDate,
          
      lTolRef,
          
      lTolTypRef,
          
      sDepot,
          
      sDescript,
          
      TOL_sName
      FROM
      ..\..\02_Data\JOB_NEW_MI.qvd
      (
      qvd);

       

      -------------------------------------------------------------------------------------CODE-----------------------------------------------------------------------------

       

       

       

      1. Do I have to save my loaded data into qvd s in order to work wiith them later on?

      2. I dont really know how to drop the Tables

       

      //Drop Table TOL_NEW; 

      was not working on register nr 1

       

      so I was always using the script :

       

      let numTables = NoOfTables();for i=1 to $(numTables)let tt = TableNAme(0);drop table [$(tt)];next

       

       

      3) Am  I on the right way or are there some bigger mistakes in my procedure??

       

       

      Thanks in advance!

       

       

      chesterluck