3 Replies Latest reply: Apr 29, 2010 6:33 AM by kellettb RSS

    Table Aliases in LOAD statement?

      I have a table which holds data about enquiries. Each Enquiry has an ID. Each Enquiry can have several follow ups and each has their own row in the same table with a sequential sequence number for that Enquiry. Each Row has a timestamp when it was inserted into the Database.

      I want to create a table In QlikView that will hold only one row for each Enquiry ID, that will also have a column which will hold the TimeStamp record from the Sequence number 0 row for that enquiry, and another column that will store the timestamp record from the sequence number 1 row for that same enquiry.

      If I was trying to do this in an SQL statement I would just use table aliases, but I am unsure of if you can do this in QlikView or the syntax if you can. I have tried the following:

       


      CTS_ENQUIRIES_RESPONSETIME:
      BUFFER
      (Stale After 24 Hours)
      LOAD
      Distinct If(SEQUENCENO=0,ENQUIRYID) As ENQUIRY,
      If (SEQUENCENO=0, TIMESTARTED) AS "LOGDATE",
      If(SEQUENCENO=1, TIMESTARTED) AS "FOLLOWUPDATE";

      SQL
      SELECT * FROM CRMUSER."CTS_ENQUIRIES2";


       

      But as I expected before even trying it I get the following table structure with the two timestamp dates on different rows.

      error loading image

       

      Does anyone know a simple solution to this? I am sure there is one! Can you use a while loop in a load statement to build a table?

       

      Thanks
      Barry

        • Table Aliases in LOAD statement?

          Hi Barry,

          I'm not sure if this will work - you may need to load the data into a qvd file first - but you should be able to do this using Join.

          eg:

           


          Load
          Distinct If (SEQUENCENO=0, ENQUIRYID) As Enquiry,
          If (SEQUENCENO=0, TIMESTARTED) As "LOGDATE";
          JOIN
          Load
          Distinct If (SEQUENCENO=1, ENQUIRYID) As Enquiry,
          If (SEQUENCENO=1, TIMESTARTED) As "FOLLOWUPDATE";


          This should join the data on the (matching) Enquiry field.

          hth,

          Tony

          • Table Aliases in LOAD statement?

            If you know how to write the query you want in SQL, why not write it in SQL? Just replace the SQL SELECT statement in your screen shot above, then you can do everything you know how to do in SQL, without having to fiddle with QV syntax.