5 Replies Latest reply: Mar 13, 2015 3:10 AM by purushotham m RSS

    SQL Create and Insert Table and join to qvd

      Hi All,

      I have a payments table that is showing the max number of payments a customer has made and if they are still paying or they have cancelled:

       

      Customer ID# Payments
      Payment Status
      1234Cancelled
      4565Paying
      7892Paying
      0121Cancelled

       

      But I want to see a row for ever time they pay:

       

      e.g:

      Customer ID
      Payment
      Payment Status
      1231Paid
      1232Paid
      1233Paid
      1234Cancelled
      4561Paid
      4562Paid
      4563Paid
      4564Paid
      4565Paid
      7891Paid
      7892Paid
      0121Cancelled

       

      In SQL I have been able to use a Create Table and Insert Into:

       

      CREATE TABLE #num

      ( i INT NOT NULL

      , PRIMARY KEY

      ) ;

      INSERT INTO #num

      VALUES

        (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18),

        (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36),  

        (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54)

       

      That I then join to my payments table by joining on the max number of payments and looking up the status:

       

      select t.*, #num.i as [Tenure Month],

      case when t.[NumberOfPayments] = #num.i then t.[PaymentStatus] else 'Paid' end as [PaymentStatus]

      from

      Payments t

      join #num

      on #num.i <= t.[NumberOfPayments]

       

       

      I cannot get this to work in Qlikview! The problem seems to be with the create and insert statements.

      Any help would be much appreciated!

      Thanks