2 Replies Latest reply: Feb 1, 2016 7:03 PM by David Maurice RSS

    Synthetic Table creation overriding user table creation?

    David Maurice

      Hi, just a quick question here about the desired behaviour when a Synthetic Table is created.

       

      My scenario is this - imagine "customers" which may have multiple names for a given month over time. We want a table with one name per customer for the most recent month.

      Now, this can be done easily inside a DBMS but doing it entirely in the Qlik data load step showed some funny behaviour.

       

      Taking my test data:

      testdata:

      load * inline

      [

      mth, CustomerID, CustomerName

      4, 1, 'fred'

      4, 1, 'tom'

      3, 1, 'fred'

      2, 1, 'fred'

      4, 2, 'phil'

      4, 3, 'jack'

      3, 4, 'sam'

      ]

      ;

       

      I create a table containing one name per month and customer, then the most recent month by customer. These tables are then inner-joined to get the most recent month and name by customer.

      If the two intermediate tables have exactly the same column names, in the data load step Qlik creates a synthetic key table, part of which is our desired output. The table even though it is specifically called on to be created in the script is not created!

      However, if the column names are not the same and no synthetic key is created, the table is created as per the script!

       

       

      Using the test data above, this replicates the problem:

      //Get one entry per customer per month
      CustomerMaster:
      LOAD
      mth as ReferenceMonth,
          CustomerID,
          MAXSTRING(CustomerName) as CustomerName2
      resident
      testdata
      GROUP BY mth, CustomerID
      ;

      //CREATE MOST RECENT ENTRY
      CustomerRecent:
      LOAD
      CustomerID,
          MAX(ReferenceMonth) as ReferenceMonth //If this is called "ReferenceMonth", the table creation below fails. If it has another name, the table is created as per the script
      resident
      CustomerMaster
      GROUP BY CustomerID
      ;


      //CREATE LIST OF MOST RECENT NAMES
      CustomerNames:
      load
      ReferenceMonth as ReferenceMonth,
          CustomerID
      resident CustomerRecent;
      INNER JOIN
      load
      ReferenceMonth,
          CustomerID,
          CustomerName2
      resident CustomerMaster;
      ;

      //CLEAN UP UN-NEEDED TABLES
      drop table CustomerMaster, CustomerRecent

        • Re: Synthetic Table creation overriding user table creation?
          Stefan Wühl

          I think that's because QV will auto-concatenate tables with same number and name of fields.

           

          Try

           

          Using the test data above, this replicates the problem:

          //Get one entry per customer per month
          CustomerMaster:
          LOAD
          mth as ReferenceMonth,
              CustomerID,
              MAXSTRING(CustomerName) as CustomerName2
          resident
          testdata
          GROUP BY mth, CustomerID
          ;

          //CREATE MOST RECENT ENTRY
          CustomerRecent:
          LOAD
          CustomerID,
              MAX(ReferenceMonth) as ReferenceMonth //If this is called "ReferenceMonth", the table creation below fails. If it has another name, the table is created as per the script
          resident
          CustomerMaster
          GROUP BY CustomerID
          ;

           

          /CREATE LIST OF MOST RECENT NAMES
          CustomerNames:
          NOCONCATENATE load
          ReferenceMonth as ReferenceMonth,
              CustomerID
          resident CustomerRecent;
          INNER JOIN
          load
          ReferenceMonth,
              CustomerID,
              CustomerName2
          resident CustomerMaster;
          ;

          //CLEAN UP UN-NEEDED TABLES
          drop table CustomerMaster, CustomerRecent