4 Replies Latest reply: Feb 10, 2018 11:38 AM by Jerry Jackson RSS

    How to split one qlik table into multiple tables based on conditions?

    DILIP SOLANKI

      Hi,

       

      I have one table called Documents. In Documents table, I have one column "DocumentLevelKey" which will have numeric value 1 to 3.

       

      Please find the meaning of this numeric value:

       

      Numeric ValueDetails
      1Geo level document.
      2City level document.
      3Country level document.

       

      I want to split "Documents" table based on DocumentLevelKey, I want to have following tables:

       

      1. GeoDocucments
      2. CityDocuments
      3. CountryDocuments

       

      Script of Document table:


      //Qlik internal table

      LOAD

              [Id] AS [CMM_Document.Id],

              [DocumentLevelKey],

              [LevelId] AS [GeographyCode],

              [DocumentDesc] AS DocumentDescription,

              [Status] AS [CMM_Document.Status],

              [Delete Ind] AS [CMM_Document.DeleteInd],

              CreatedBy AS [CMM_Document.CreatedBy],

              UpdatedBy AS [CMM_Document.UpdatedBy],

              Date([Created Date], '$(TimestampFormat)') AS [CMM_Document.CreatedDate],

              Date([UpdatedDate],'$(TimestampFormat)') AS [CMM_Document.UpdatedDate];

         

      //SQL table

      [CMM_Document]:

      SELECT

      Id,

      DocumentLevelKey,

      LevelId,

      [DocumentDesc],

      [Status],

      [Delete Ind],

      CreatedBy,

      [Created Date],

      UpdatedBy,

      UpdatedDate

      FROM CMM_Document

      WHERE [Status] = 'A' AND [Delete Ind] = 'N';


      STORE CMM_Document INTO '$(vQVDPath)CMMDocument_QVD.Qvd' (qvd);

      DROP TABLE CMM_Document;


      [CMMDocument_QVD]:

      LOAD * FROM

      [$(vQVDPath)CMMDocument_QVD.Qvd](qvd);

       

      [CMMDocument_Geo_QVD]:

      LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=1;

       

      [CMMDocument_City_QVD]:

      LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=2;

       

      [CMMDocument_Country_QVD]:

      LOAD * RESIDENT CMMDocument_QVD WHERE DocumentLevelKey=3;

       

      As you can see in above query, I have DocumentLevelKey column, I want to split table using where clause. I want CMMDocument_Geo_QVD, CMMDocument_City_QVD and CMMDocument_Country_QVD tables to be generated.

       

      Can anyone suggest how can I achieve this?

       

      Thanks

      Dilip Solanki

       

      Message was edited by: DILIP SOLANKI